Oracle SQL Insert Statement with Critieria

with the necessary values

Will they be copied from other existing rows (orders)?

Is this rather about finding gaps or what do you aim at?

No, I will load the values myself in the statement.
Just wanted to figure out the correct way to code the where statement.

An INSERT statement is pretty straight forward and there are examples all over the web:
insert into your_table(col1,col2) values(80,'Hello');

Something tells me what you are really after is more complicated.

Yeah, I also think, that a straight INSERT INTO will be way too simple ;-)

It could be. Currently, in my dept table there are many departments that can have an array of order numbers.
I just want to make sure that I only insert order number 80 into the departments that don't have that one listed.
In the file i uploaded, you can see examples of departments that don't have an order number of 80 and those that do.

Based on your described sample data a simple insert does seem best, as stated by others above.

BUT...

My guess is, you are actually asking about something more complicated, that is, what if you get data for department 12345 again with orders 80 and 90.

You already have order 90, so you only want to insert 80.

You have some options
 

• You can insert both rows and let a unique constraint block 90 and then either do nothing with the error or handle the condition in some way

 

• You can query the existing data to see if 90 is there before you insert.  If you find it, then either do nothing, or handle the condition in some way

• You can use a one-sided MERGE statement that only does inserts.  This assumes you want to do nothing when a matching id is already found.

In the file i uploaded

No file here...

Maybe, you're looking for something like "insert into ... select .... where not exists ...." or some sort of merge?!

in your case, you need to use:

insert into department
(field1, field2)
select b.field1, b.field2 from another a
left join department b on a.orderno = b.orderno
where b.orderno is null

So you all are saying a simple insert statemtent would work.
Would i write it as this:
insert into your_table(col1,col2) values(80,'PRESIDENT')
where order_number <>80;
Workflow-Examples.docx

Would i write it as this:
insert into your_table(col1,col2) values(80,'PRESIDENT')
where order_number <>80;

No, that won't work this way. You need to stick to what ststuber/me mentioned above!

Alex,

I want to try your method: "insert into ... select .... where not exists ...."

Would it look like this:
insert into your_table(col1,col2) values(80,'PRESIDENT')
(select * where not exisits order_number 80);

It would be something like this:

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )

Select allOpen in new window

Taken from: https://stackoverflow.com/questions/3841441/oracle-how-to-insert-if-a-row-doesnt-exist