IF not exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF not exists

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 08:18:59

Need to run a script that checks a table and if certain records do not exist I need to move records to antoher table.

Tables: OEORDLIN, ORDERREBATE, ORDERREBATEHISTORY

ALL 3 TABLES CONTAIN ORD_TYPE, ORD_NO, ITEM_NO, LIN_SEQ_NO. These fields are my key.

When the script is ran, if the records that are in the orderrebate table no longer exist in the oeordlin, I need to remove the records from the orderrebate table and insert them into the orderrebatehistory table.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-04 : 08:27:02

May be this-:

INSERT INTO orderrebatehistory
select ORB.* from orderrebate ORB Left join oeordlin ORD on ORB.ORD_TYPE=ORD.ORD_TYPE
AND ORB.ORD_NO=ORD.ORD_NO AND ORB.ITEM_NO=ORD.ITEM_NO AND ORB.LIN_SEQ_NO=ORD.LIN_SEQ_NO WHERE ORD.ORD_NO IS NULL and ORD.ITEM_NO AND ORD.LIN_SEQ_NO IS NULL AND ORD.ORD_TYPE IS NULL

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-04 : 08:40:59

A slight modification in Sunil's suggestion

INSERT INTO orderrebatehistory
select ORB.* from orderrebate ORB Left join oeordlin ORD on ORB.ORD_TYPE=ORD.ORD_TYPE
AND ORB.ORD_NO=ORD.ORD_NO AND ORB.ITEM_NO=ORD.ITEM_NO AND ORB.LIN_SEQ_NO=ORD.LIN_SEQ_NO WHERE ORD.ORD_NO IS NULL and ORD.ITEM_NO AND ORD.LIN_SEQ_NO IS NULL AND ORD.ORD_TYPE IS NULL

delete orderrebate
from orderrebatehistory orh
inner join orderrebate orb on ( ORB.ORD_TYPE=ORh.ORD_TYPE
AND ORB.ORD_NO=ORh.ORD_NO AND ORB.ITEM_NO=ORh.ITEM_NO AND ORB.LIN_SEQ_NO=ORh.LIN_SEQ_NO )

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 08:49:55

Not understanding ORB.*

Is that just an abbreviation??

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-04 : 08:53:04

quote:
Originally posted by Vack

Not understanding ORB.*

Is that just an abbreviation??

ORB is an alias name for 'orderrebate' table. Instead of using many times the same table name we defined an alias name ORB and used ORB wherever we have to use 'orderrebate' table

'*' indicates all columns in a table

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 09:00:01

I receive this error when running.

Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-04 : 09:14:00

Sorry missed one IS NULL.

INSERT INTO orderrebatehistory
select ORB.* from orderrebate ORB Left join oeordlin ORD on ORB.ORD_TYPE=ORD.ORD_TYPE
AND ORB.ORD_NO=ORD.ORD_NO AND ORB.ITEM_NO=ORD.ITEM_NO AND ORB.LIN_SEQ_NO=ORD.LIN_SEQ_NO WHERE ORD.ORD_NO IS NULL and ORD.ITEM_NO IS NULL AND ORD.LIN_SEQ_NO IS NULL AND ORD.ORD_TYPE IS NULL

delete orderrebate
from orderrebatehistory orh
inner join orderrebate orb on ORB.ORD_TYPE=ORh.ORD_TYPE
AND ORB.ORD_NO=ORh.ORD_NO AND ORB.ITEM_NO=ORh.ITEM_NO AND ORB.LIN_SEQ_NO=ORh.LIN_SEQ_NO

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-04 : 09:16:00

quote:
Originally posted by Vack

I receive this error when running.

Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'

Posted - 09/04/2008 : 08:40:59
--------------------------------------------------------------------------------

Try this

INSERT INTO orderrebatehistory
select ORB.* from orderrebate ORB Left join oeordlin ORD on ORB.ORD_TYPE=ORD.ORD_TYPE
AND ORB.ORD_NO=ORD.ORD_NO AND ORB.ITEM_NO=ORD.ITEM_NO AND ORB.LIN_SEQ_NO=ORD.LIN_SEQ_NO WHERE ORD.ORD_NO IS NULL and ORD.ITEM_NO IS NULL AND ORD.LIN_SEQ_NO IS NULL AND ORD.ORD_TYPE IS NULL

delete orderrebate
from orderrebatehistory orh
inner join orderrebate orb on ( ORB.ORD_TYPE=ORh.ORD_TYPE
AND ORB.ORD_NO=ORh.ORD_NO AND ORB.ITEM_NO=ORh.ITEM_NO AND ORB.LIN_SEQ_NO=ORh.LIN_SEQ_NO )

Note: keep IS Null after ORD.ITEM_NO in Where clause

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 12:58:50

I'm not sure of your business rules, but perhaps you might want to use a trigger to make sure the deleted records are moved at the time they are deleted? Or when 2008 is relased you might be able to make use of the Changed Data Captute system (CDC)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 13:01:03

related thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110027

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources