- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- INSERT STATEMENT
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 05:58 AM
тАО10-02-2002 05:58 AM
INSERT STATEMENT
I have a question regarding an INSERT STATEMENT.
We use an insert statement
INSERT INTO MyTable(
field 1,
field 2,
...
...
field 20
VALUES(
Onhand_rec.upn,
...
...
onhand_rec.qty
...
);
However I only want records with a quanty (onhand_rec.qty)>0.
My quick and dirty solution was:
DELETE FROM MYTABLE
WHERE qty =0;
Is it possible to check the value of QTY with the above INSERT STATEMENT?
Regards,
Sander
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 06:44 AM
тАО10-02-2002 06:44 AM
Re: INSERT STATEMENT
in the insert statement??
Or use perl
live free or die
harry
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 06:47 AM
тАО10-02-2002 06:47 AM
Re: INSERT STATEMENT
This assumes that the columns from mytable match onhand_rec in their format, otherwise you will need to insert default values as text in the select statement, or format conversions on the fields.
My recommendation:- DBA or SQL course, as soon as!
Share and Enjoy! Ian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 11:35 PM
тАО10-02-2002 11:35 PM
Re: INSERT STATEMENT
My mistake i didn't say that onhand_rec is a record in a cursor called onhand_cur.
So again:
is it possible to create something like this:
INSERT INTO MyTable
...
...
qty
...
VALUES
(SELECT
...
...
onhand_rec.qty
...
FROM onhand_cur
WHERE onhand_rec.qty > 0)
Sander
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 11:48 PM
тАО10-02-2002 11:48 PM
Re: INSERT STATEMENT
I think that the last sentence is fine if you want to use a cursor and there are other posibity that is to use the insert as select statment.
Regards,
Justo.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2002 11:59 PM
тАО10-02-2002 11:59 PM
Re: INSERT STATEMENT
No you cannot do "
SELECT ..
FROM cursor
WHERE (...)
"
You would have to put the WHERE clause into the cursor body.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-03-2002 03:46 AM
тАО10-03-2002 03:46 AM
Re: INSERT STATEMENT
1) You have to clean your table from your bad records
2) change your table definition and add a constraint on "qty"
cf. Oracle documentation on constraint
CREATE TABLE order_detail
(CONSTRAINT pk_od PRIMARY KEY (order_id, part_no),
order_id NUMBER
CONSTRAINT fk_oid REFERENCES scott.order (order_id),
part_no NUMBER
CONSTRAINT fk_pno REFERENCES scott.part (part_no),
quantity NUMBER
CONSTRAINT nn_qty NOT NULL
CONSTRAINT check_qty_low CHECK (quantity > 0),
cost NUMBER
CONSTRAINT check_cost CHECK (cost > 0) );
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-03-2002 04:57 AM
тАО10-03-2002 04:57 AM
Re: INSERT STATEMENT
SQL> spool test_insert.log
SQL>
SQL> create table test_inserts as
2 select * from hrd_index_stats
3 where blocks between 0 and 100
4 ;
Table created.
SQL>
SQL> insert into test_inserts
2 (select * from hrd_index_stats
3 where blocks > 1000)
4 ;
1339 rows created.
SQL>
SQL> Drop table test_inserts;
Table dropped.
SQL> spool off