Udate based on 2 matches? - 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

Udate based on 2 matches?

Author

Topic

Wabby
Starting Member

27 Posts

Posted - 2008-10-10 : 05:26:21

Hi Guys,

I an trying to update a table, based on iformation in another table.

Table1 has the following fields:

TransID, UsageID, CodeID, ItemID

Table 2 has the following fields:

StockCode, UsageID, CodeID, ItemId

Table 2 is a staging table that I have imported data into from a txt file, and I wish to achieve the following:

I want to update CodeID in table one, but ONLY if table1.usageid = table2.usageid AND table1.itemid = table2.itemid

I have tried the following, but it fails.

UPDATE Table1
SET Table1.CodeID = Table2.CodeID
WHERE Table1.UsageID=Table2.UsageID AND Table1.ItemId=Table2.ItemID

It throws an error :(

Any help is much appreciated :)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-10 : 05:31:01

update t1
set t1.codeID = t2.codeID
from table1 t1 join table2 t2
on T1.UsageID=T2.UsageID AND T1.ItemId=T2.ItemID

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 05:31:08

[code]UPDATE Table1
SET Table1.CodeID = Table2.CodeID
FROM Table1
JOIN Table2
ON Table1.UsageID=Table2.UsageID
AND Table1.ItemId=Table2.ItemID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 05:31:44

by 36 secs

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-10 : 05:32:16

ooohh... close

Wabby
Starting Member

27 Posts

Posted - 2008-10-10 : 05:50:03

Good stuff - You guys come to the rescue again :)

I take it I needed the join to make sure that both data fields matched before an udate was executed?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-10 : 05:51:46

sort of, but in really simple terms you were referencing table2, but didn't even have a FROM clause

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources