UPDATE on Table Based on Matching Columns

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 on Table Based on Matching Columns

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2008-04-08 : 14:28:20

I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".

ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A

I want the result to be:

ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC

I am stumped as to how to do this - any help or advice would be appreciated.

The only thing I've come up with is:

UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')

But when I run it, it says "0 rows affected".

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 14:37:44

update I
set code = 'I' + M.code
from tbltest I
join tbltest M
on I.id = M.ID
and M.LOC = 'MAIN'
where I.LOC = 'INT.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:38:22

[code]DECLARE @Sample TABLE (ID INT, Loc VARCHAR(4), Code VARCHAR(2))

INSERT @Sample
SELECT 11, 'MAIN', 'B' UNION ALL
SELECT 11, 'INT', 'A' UNION ALL
SELECT 22, 'MAIN', 'C' UNION ALL
SELECT 22, 'INT', 'A'

SELECT *
FROM @Sample

UPDATE s
SET s.Code = 'I' + x.Code
FROM @Sample AS s
INNER JOIN @Sample AS x ON x.ID = s.ID
AND x.Loc = 'Main'
WHERE s.Loc = 'INT'

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:38:47

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 14:42:48

Note to Peso
If you can't type as fast as Tara it's a lot quicker if you don't try to test the result :}.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2008-04-08 : 15:36:44

Thank you both, very elegantly done - it seems to work great. Maybe one day SQL will seem intuitive to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources