Update based on different values
-
Hi !!
I have to update DOB in table2 from the DOB in table1 for ROLL Id's and if any ROLLID has status both as U and A , then we should consider
the record with status A.
Table1
ROLL STATUS DOB
ROLL1 U XXXX
ROLL2 A XXXX
ROLL3 A XXXX
ROLL4 A XXXX
ROLL4 U ZZZZ
ROLL2 U YYYY
TABLE2
ROLL DOB
ROLL1
ROLL2
ROLL3
ROLL4
P.S: DATA in table1 is basically a result of select query for some ROLL ID's , i've just given as table1 to make it clear.
PLease help, i was trying to use grroup by and having but it's throwing error
-
Why don't you just write it as two update statements? One that does all the 'A' records, and one that does all the 'U' records that also don't have a DOB in Table2.
update Table2set DOB = Table1.DOBfrom Table2inner joinon Table1.Roll = Table2.Rollwhere Status = 'A'update Table2set DOB = Table1.DOBfrom Table2inner join Table1 on Table1.Roll = Table2.Rollwhere Status = 'U'and Table1.DOB is null -
Thanks SQLZ!!
But this was not applicable in my real situation as there was already data , so no chance of checking it as null. Sorry i didn't mention it earlier .
Though , i implemented it otherwise by populating required data in a temp table and updating using this data .
Please see the code below :
SELECT TABLE1.ROLL , COUNT(TABLE1.ROLL) AS COUNT, TABLE1.DOB, TABLE1.STATUS
INTO #TEMP
FROM TABLE1 ,TABLE2 where
TABLE1.ROLL = TABLE2.ROLL
GROUP BY TABLE1.ROLL,TABLE1.STATUS,TABLE1.DOB
HAVING ((COUNT(TABLE1.ROLL) > 1 AND TABLE1.STATUS = 'A') OR COUNT(TABLE1.ROLL)= 1 )
UPDATE TABLE2
SET TABLE2.DOB = T.DOB
FROM TABLE2, #temp T WHERE
TABLE2.ROLL = T.ROLL
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply