Nested If inside a Loop

Question

Post reply

Nested If inside a Loop

adi.cohen

Old Hand

Points: 371
More actions
June 12, 2008 at 6:13 am

#381065

Hi,
I need to create a query that for each row in a table, checks for a certain condition, and if this is True - Delete a row from another table where RowNum(this is the Unique identifier of both tables) equals the RowNum we checked.
if Not - update the other table.
I'm having a syntax problems and can't finds how to do that, can anyone please advice?
thanks !
Adi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

Please could you post the query that you have so far, so that we can point you in the right direction

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

adi.cohen Old Hand Points: 371 More actions · Answer 2

Hi,

this is the query I wrote, this is the first time I write these kind of queries..... 🙁

I have 2 tables, both have EE_ID as the unique identifer, both have Phone field.

I would prefer not to use a Done field, but found an example of this in order to go through the rows.....

thanks!

SET NOCOUNT ON

DECLARE @i INT

SELECT @i = MIN(EE_ID)

FROM EeUpdates_T

WHERE Done = 0

WHILE @i IS NOT NULL

BEGIN

if EeUpdates_T.Phone=(select HomePhone from [EeTemp_T] WHERE [EeTemp_T].EE_ID=@i)

then (DELETE FROM [HR_Staffing].[dbo].[EeUpdates_T] WHERE [EeUpdates_T].EE_ID=@i) and (SELECT @i = MIN(EE_ID) FROM EeUpdates_T WHERE Done = 0)

else (update EeTemp_T set EeTemp_T.HomePhone=EeUpdates_T.Phone where EeTemp_T.EE_ID=@i and EeUpdates_T.EE_ID=@i)

end if

END

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

mmm

OK what is the Temp table for?

You should beable to simply run an update statement followed by the Delete statement with not loops or if statements on all the rows in go, provided you have the correct where clause!

Does this make sense?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

UPDATE [T]

SET [T].HomePhone = .Phone

FROM EeTemp_T [T]

INNER JOIN EeUpdates_T

ON .[EE_ID] = [T].[EE_ID]

WHERE .Phone NOT IN (select HomePhone from [EeTemp_T])

DELETE

FROM [EeUpdates_T]

WHERE .Phone IN (select HomePhone from [EeTemp_T])

YOu could change the Where clauses to be Joins I just did it like this as it's easier to read 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

adi.cohen Old Hand Points: 371 More actions · Answer 5

thanks for that! I'll test it and let you know:)

adi.cohen Old Hand Points: 371 More actions · Answer 6

Thanks, I've made some adjustments and this is working !!!

much more simple than I thoguht 🙂

thanks again

Good Night,

Adi