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

update record from another record in same table Expand / Collapse
Author
Message
Posted Wednesday, May 01, 2013 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:47 PM
Points: 146, Visits: 301
Hi, I'm looking for the best way to update pkid 1 = pkid 2

update testTable
set
a.fname = b.fname
from
(select * from testTable where pkid = 1) as a
join
(select * from testTable where pkid = 2) as b

Post #1448451
Posted Wednesday, May 01, 2013 9:03 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
Marcus Farrugia (5/1/2013)
Hi, I'm looking for the best way to update pkid 1 = pkid 2

update testTable
set
a.fname = b.fname
from
(select * from testTable where pkid = 1) as a
join
(select * from testTable where pkid = 2) as b



This:


update dbo.testTable set
fname = (select fname from dbo.testTable where pkid = 2)
where
pkid = 1;





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 #1448455
Posted Wednesday, May 01, 2013 9:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:47 PM
Points: 146, Visits: 301

update dbo.testTable set
fname = (select fname from dbo.testTable where pkid = 2)
where
pkid = 1;




Thanks Lynne!
if there were multiple columns would I just do the same for each column, ie:

update dbo.testTable set
fname = (select fname from dbo.testTable where pkid = 2),
lname = (select fname from dbo.testTable where pkid = 2),
address = (select address from dbo.testTable where pkid = 2
where
pkid = 1;
Post #1448482
Posted Thursday, May 02, 2013 5:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
Marcus Farrugia (5/1/2013)

update dbo.testTable set
fname = (select fname from dbo.testTable where pkid = 2)
where
pkid = 1;




Thanks Lynne!
if there were multiple columns would I just do the same for each column, ie:

update dbo.testTable set
fname = (select fname from dbo.testTable where pkid = 2),
lname = (select fname from dbo.testTable where pkid = 2),
address = (select address from dbo.testTable where pkid = 2
where
pkid = 1;


Maybe you could try something along these lines if you have multiple columns:

UPDATE tt1 
SET FName = tt2.FName,
LName = tt2.LName,
[Address] = tt2.[Address]

FROM dbo.TestTable tt1 JOIN
(SELECT 1 as pkid, FName, LName, [Address] FROM dbo.TestTable WHERE pkid=2) tt2
ON tt1.pkid = tt2.pkid
WHERE tt1.pkid=1

Please note it is untested as you have supplied no test data or ddl.
Post #1448769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse