Update based on different values

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 2000 Forums

SQL Server Development (2000)

Update based on different values

Author

Topic

vikashsql
Starting Member

6 Posts

Posted - 2008-05-15 : 04:55:11

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

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-15 : 05:48:31

hi
try this

declare @tb1 table(roll varchar(20),status varchar(20),dob varchar(20))
insert @tb1
select 'ROLL1', 'U','XXXX'
union all select 'ROLL2', 'A','XXXX'
union all select 'ROLL3', 'A', 'XXXX'
union all select 'ROLL4', 'A','XXXX'
union all select 'ROLL4', 'U', 'ZZZZ'
union all select 'ROLL2' ,'U', 'YYYY'

declare @tb2 table(roll varchar(20),dob varchar(20))
insert @tb2
select 'ROLL1','null'
union all select 'ROLL2','null'
union all select 'ROLL3' ,'null'
union all select 'ROLL4' ,'null'

delete from @tb1 where status='u' and roll in
(select min(roll) from @tb1 group by roll having count(*) > 1)

UPDATE t
SET t.dob=yt.dob
FROM @tb1 yt
INNER JOIN @tb2 t
ON t.roll=yt.roll
select * from @tb2

ok tanx....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-15 : 05:57:36

[code]declare @tb1 table(roll varchar(20),status varchar(20),dob varchar(20))
insert @tb1
select 'ROLL1', 'U','XXXX'
union all select 'ROLL2', 'A','XXXX'
union all select 'ROLL3', 'A', 'XXXX'
union all select 'ROLL4', 'A','XXXX'
union all select 'ROLL4', 'U', 'ZZZZ'
union all select 'ROLL2' ,'U', 'YYYY'

declare @tb2 table(roll varchar(20),dob varchar(20))
insert @tb2
select 'ROLL1','null'
union all select 'ROLL2','null'
union all select 'ROLL3' ,'null'
union all select 'ROLL4' ,'null'

update t2
set dob = t1.dob
from @tb2 t2 join (select roll, min(status) as status from @tb1 group by roll ) t on t2.roll = t.roll
join @tb1 t1 on t.roll = t1.roll and t.status = t1.status

select * from @tb2[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vikashsql
Starting Member

6 Posts

Posted - 2008-05-15 : 10:03:03

Thanks Man!
I think this will not be the apt solution here as i've more than 100 reocrds :(

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

vikashsql
Starting Member

6 Posts

Posted - 2008-05-15 : 10:11:34

quote:

Originally posted by harsh_athalye

declare @tb1 table(roll varchar(20),status varchar(20),dob varchar(20))
insert @tb1 
select 'ROLL1', 'U','XXXX'
union all select 'ROLL2', 'A','XXXX'
union all select 'ROLL3', 'A', 'XXXX'
union all select 'ROLL4', 'A','XXXX'
union all select 'ROLL4', 'U', 'ZZZZ'
union all select 'ROLL2' ,'U', 'YYYY'

declare @tb2 table(roll varchar(20),dob varchar(20))
insert @tb2 
select 'ROLL1','null'
union all select 'ROLL2','null'
union all select 'ROLL3' ,'null'
union all select 'ROLL4' ,'null'

update t2
set dob = t1.dob
from @tb2 t2 join (select roll, min(status) as status from @tb1 group by roll ) t on t2.roll = t.roll
join @tb1 t1 on t.roll = t1.roll and t.status = t1.status

select * from @tb2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks Man!!
I already got it , problem was that i was trying to it one go .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources