Need help with IF, ELSE, UPDATE script

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with IF, ELSE, UPDATE script

Author

Topic

dt293
Starting Member

8 Posts

Posted - 2007-05-10 : 09:58:40

Hello there....I am new to sql and need to create a script. I know what I want it to do but dont know how to code it....
Basically I have two tables (Resluts and ADUsers) that I would like to join on a particular field (Logon). I would like to return the rows that DO NOT match and insert them into a 3rd table called MissingADUsers.

If they already exist (based on the logon field) I would like a status field to be updated from 1 to 2 then 3.

I think it should go something like this...

SELECT * FROM RESULTS, ALL ADUSERS
INNER JOIN ON LOGON
WHERE LOGON <> EXIST IN RESULTS
IF <> EXIST INSERT INTO MISSINGADUSER
BEGIN
IF EXISTS AND STATUS FLAG = 1
THEN UPDATE STATUS TO 2
ELSE
IF EXISTS AND STATUS FLAG = 2
THEN UPDATE STATUS TO 3

Any help would be appreciated SQL gurus!

Cheers

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-10 : 10:18:24

Use two statements: one for the insert and one for the update.

INSERT INTO dbo.MissingADUsers (Field1, Field2, etc.)
SELECT      a.Field1, a.Field2, etc.
FROM        dbo.ADUsers AS a
LEFT JOIN   dbo.Results AS r
ON          a.Logon = r.Logon
WHERE       r.Logon IS NULL

UPDATE      a
SET         a.StatusFlag = CASE a.StatusFlag 
                             WHEN 1 THEN 2 
                             WHEN 2 THEN 3 
                           END
FROM        dbo.ADUsers AS a
JOIN        dbo.Results AS r
ON          a.Logon = r.Logon
WHERE       a.StatusFlag BETWEEN 1 AND 2

Mark

dt293
Starting Member

8 Posts

Posted - 2007-05-10 : 10:47:36

That is great, thank you very much.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-10 : 13:51:27

No problem!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources