Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

INSERT STATEMENT

Sander Derix
Occasional Advisor

INSERT STATEMENT

Hi group,

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
7 REPLIES
harry d brown jr
Honored Contributor

Re: INSERT STATEMENT

WHERE qty > 0

in the insert statement??

Or use perl


live free or die
harry
Live Free or Die
Ian Dennison_1
Honored Contributor

Re: INSERT STATEMENT

insert into mytable (select upn, ... ..., qty from onhand_rec where qty > 0);

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
Building a dumber user
Sander Derix
Occasional Advisor

Re: INSERT STATEMENT

Hi IAN,

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
Justo Exposito
Esteemed Contributor

Re: INSERT STATEMENT

Hi Sander,

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.
Help is a Beatiful word
Graham Cameron_1
Honored Contributor

Re: INSERT STATEMENT

Sander
No you cannot do "
SELECT ..
FROM cursor
WHERE (...)
"

You would have to put the WHERE clause into the cursor body.
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Jean-Luc Oudart
Honored Contributor

Re: INSERT STATEMENT

Sanders,

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
fiat lux
Raynald Boucher
Super Advisor

Re: INSERT STATEMENT

The answer is yes (as shown below)

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