Update Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update Query

Author

Topic

newuser12
Starting Member

6 Posts

Posted - 2009-03-11 : 01:24:56

have 3 tables
tb1

ID Amt1 Amt2 Amt3 Type
1 100 200 100 NULL
2 400 600 500 NULL
3 200 300 200 NULL
4 900 400 250 NULL

tb2 - have the min Amt for each ID (multiple record)

Amt ID Type
100 1 Amt1
100 1 Amt2
400 2 Amt1
200 3 Amt1
200 3 Amt3
250 4 Amt3

tb3 - have distinct min Amt for each ID
Amt ID
100 1
400 2
200 3
250 4

I want to update Type column of tb1.
if Amt value have same value for one perticluare ID the update tb1.Type = NULL
else update tb1.Type with tb2.Type
so my o/p should be

ID Amt1 Amt2 Amt3 Type
1 100 200 100 NULL
2 400 600 500 AMt1
3 200 300 200 NULL
4 900 400 250 Amt3

I am trying Case in update statement..But facing an issue.

Could you plz help me?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-11 : 01:53:47

update table tb1
set type = case when amt1 = amt3 then null
when amt1 > amt3 then 'amt3'
when amt3> amt1 then 'amt1' end

newuser12
Starting Member

6 Posts

Posted - 2009-03-11 : 02:18:12

i am trying this

Update tb1
set Type=
case when (select 1 from tb2 t1,tb3 t2
where t1.ID = t2.nID and
t1.Amt = t2.Amt) then NULL
else tb2.Type1
end

But it is returning an error

newuser12
Starting Member

6 Posts

Posted - 2009-03-11 : 02:25:20

My issue has been resolved ..Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-11 : 02:34:59

try like this
Update t
set Type= case when amt1 = amt3 then null
when amt1 > amt3 then 'amt3'
when amt3> amt1 then 'amt1' end
from @tab t
inner join (select t1.* from @tb2 t1 join @tb3 t2
on t1.ID = t2.ID and
t1.Amt = t2.Amt) tt on tt.id = t.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources