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

Happiq
Starting Member

1 Post

Posted - 2010-10-27 : 17:12:04

Hello All,

What I would like to achieve is to update one table based on rows in different table.

Table AA
LET1 LET2 (column names)
A 1
B 2
C null
D null
E 5

Table BB
LET1 LET2 (column names)
1 X
2 Y

I would like to run an update query to replace values in table AA in column LET2 with values from table BB column LET2 where AA.LET2 = BB.LET1
so I expect to see sth like that:

Table AA
LET1 LET2
A X
B Y
C null
D null
E 5

Here is what I have written and it works:
update aa set aa.let2 =
(select bb.let2 from bb where bb.let1 = aa.let2)
where aa.let2 in (select distinct bb.let1 from bb)

Is there any way to modify this query in a way so I do not have to use that last part

where aa.let2 in (select distinct bb.let1 from bb)

as this probably will cause some performance issue in case it is run against big tables

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-27 : 17:52:00

DECLARE @AA Table (Let1 varchar(5),Let2 varchar(5) )
DECLARE @BB TABLE (LET1 varchar(5),Let2 varchar(5))

INSERT INTO @AA
SELECT 'A', 1 UNION
SELECT 'B', 2 UNION
SELECT 'C', null UNION
SELECT 'D', null UNION
SELECT 'E', 5

INSERT @BB
SELECT 1,'X' UNION
SELECT 2,'Y'

UPDATE A
SET A.LET2 = B.LET2
FROM @AA as A
INNER JOIN @BB as B
ON
A.LET2 = B.LET1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources