Single Update Query - Required

Question

Post reply

Single Update Query - Required

ManiDBLover

SSC Veteran

Points: 236
More actions
March 12, 2013 at 5:29 am

#271383

Table 1:
SnoSIDSname
1NullA
2NullB
3NullC
4NullD
5NullE
Table 2:
anoaIDaName
155AA
232BB
353CC
410DD
510EE
Requirement:
I need to update Table 1 - Column SID values with Table 2 - Column aID in a single update query
Output data should be:

SnoSIDSname
155A
232B
353C
410D
510E

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

This seems quite simple like homework stuff

Can you let us know what you have tried?

If you are stuck somewhere, we are happy to help you.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

This should get you started...

IF OBJECT_ID('tempdb..#table1') IS NOT NULL

DROP TABLE #table1;

IF OBJECT_ID('tempdb..#table2') IS NOT NULL

DROP TABLE #table2;

CREATE TABLE #table1 (sno int unique NOT NULL, [sid] int NULL, sname varchar(2) unique NOT NULL);

CREATE TABLE #table2 (ano int unique NOT NULL, [aid] int NULL, aname varchar(2) unique NOT NULL);

INSERT INTO #table1

SELECT 1,Null,'A' UNION ALL

SELECT 2,Null,'B' UNION ALL

SELECT 3,Null,'C' UNION ALL

SELECT 4,Null,'D' UNION ALL

SELECT 5,Null,'E';

INSERT INTO #table2

SELECT 1,55,'AA' UNION ALL

SELECT 2,32,'BB' UNION ALL

SELECT 3,53,'CC' UNION ALL

SELECT 4,10,'DD' UNION ALL

SELECT 5,10,'EE';

-- Here's what we need #table 1 to look like:

SELECTt1.sno,

t2.aid,

t1.sname

FROM #table1 t1

JOIN #table2 t2 ON t1.sno=t2.ano;

-- This will do it (Update based on a join)

UPDATE t1

SET t1.[sid]=t2.aid

FROM #table1 t1

JOIN #table2 t2 ON t1.sno=t2.ano;

-- did it work?

SELECT * FROM #table1;

--cleanup

DROP TABLE #table1;

DROP TABLE #table2;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

robertd 77391 SSC Journeyman Points: 97 More actions · Answer 3

A simple way to do this:

update #Table1

set #Table1.SID = #Table2.aID

from #Table2

where #Table1.Sno = #Table2.Sno ;

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

robertd 77391 (3/12/2013)
A simple way to do this:
update #Table1
set #Table1.SID = #Table2.aID
from #Table2
where #Table1.Sno = #Table2.Sno ;

I can't for the life me remember the details but there is an issue when you do an UPDATE FROM and the table being updated is not in the query. It is better to use the join method as demonstrated by Alan above.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Sean Lange (3/12/2013)
robertd 77391 (3/12/2013)
A simple way to do this:
update #Table1
set #Table1.SID = #Table2.aID
from #Table2
where #Table1.Sno = #Table2.Sno ;
I can't for the life me remember the details but there is an issue when you do an UPDATE FROM and the table being updated is not in the query. It is better to use the join method as demonstrated by Alan above.

I think what is needed for this would be this:

update #Table1

set SID = (SELECT #Table2.aID from #Table2 where #Table1.Sno = #Table2.Sno) ;

ManiDBLover SSC Veteran Points: 236 More actions · Answer 6

Hi All,

Thanks for your help.

All your solutions worked!

Regards,

Mani