May 15, 2008 at 2:53 am
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
May 15, 2008 at 3:49 am
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 Table2
set DOB = Table1.DOB
from Table2
inner join
on Table1.Roll = Table2.Roll
where Status = 'A'
update Table2
set DOB = Table1.DOB
from Table2
inner join Table1 on Table1.Roll = Table2.Roll
where Status = 'U'
and Table1.DOB is null
May 15, 2008 at 7:57 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy