update query issue - 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 issue

Author

Topic

newuser12
Starting Member

6 Posts

Posted - 2009-03-10 : 06:28:52

I have following 2 tables

Table1

ID Amt AmtType
1 100 Amt1
2 200 Amt1
3 300 Amt2
3 300 Amt3
4 800 Amt1

Table2 - Currently type in Table2 iS NULL
ID Amt AType
1 100 NULL
2 200 NULL
3 300 NULL
3 300 NULL
4 800 NULL

I want to update AType of Table2 from AmtType of Table1. My output should be as follow

Table2 -
ID Amt AType
1 100 Amt1
2 200 Amt1
3 300 Amt2
3 300 Amt3
4 800 Amt1

I am facing problem to update the AType for ID = 3.
Could you please suggest,how I can update Table2 this?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 06:30:42

update t
set t.atype= a.amttype
from table2 t
inner join table1 a on a.id = t.id and a.amt= t.amt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:35:03

[code]
UPDATE t1
SET t1.AType=t2.AmtType
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID,Amt ORDER BY ID) AS Seq,ID,Amt,AType
FROM Table2)t1
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ID,Amt ORDER BY ID) AS Seq,ID,Amt,AmtType
FROM Table1)t2
ON t2.ID=t1.ID
AND t2.Seq=t1.Seq
AND t2.Amt=t1.Amt
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources