Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single Update Query - Required Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 6:39 AM
Points: 7, Visits: 16
Table 1:

Sno	SID	Sname
1 Null A
2 Null B
3 Null C
4 Null D
5 Null E


Table 2:

ano	aID	aName
1 55 AA
2 32 BB
3 53 CC
4 10 DD
5 10 EE


Requirement:
I need to update Table 1 - Column SID values with Table 2 - Column aID in a single update query

Output data should be:

 
Sno SID Sname
1 55 A
2 32 B
3 53 C
4 10 D
5 10 E

Post #1429693
Posted Tuesday, March 12, 2013 5:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1429697
Posted Tuesday, March 12, 2013 3:06 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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:
SELECT t1.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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1430118
Posted Tuesday, March 12, 2013 3:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 1, 2013 3:43 PM
Points: 3, Visits: 7
A simple way to do this:


update #Table1
set #Table1.SID = #Table2.aID
from #Table2
where #Table1.Sno = #Table2.Sno ;


Post #1430123
Posted Tuesday, March 12, 2013 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 13,301, Visits: 12,159
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430125
Posted Tuesday, March 12, 2013 3:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:19 PM
Points: 23,286, Visits: 32,012
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) ;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1430129
Posted Friday, March 15, 2013 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 6:39 AM
Points: 7, Visits: 16
Hi All,

Thanks for your help.

All your solutions worked!


Regards,
Mani
Post #1431491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse