1748117 Members
3639 Online
108758 Solutions
New Discussion юеВ

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 7
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