0

I am using SQL Server 2012.

I want to update a row in my table so that it equals the values of another row in the same table apart from the primary key field, see example below. What is the best way to do this?

 Primary Key      Field One     Field Two    Field Three
 ABS              5             6            2
 NJK              3             2            3

So the update query should return a result that looks like,

 Primary Key      Field One     Field Two    Field Three
 ABS              5             6            2
 NJK              5             6            2
mHelpMe
  • 6,336
  • 24
  • 75
  • 150

2 Answers2

1
UPDATE  A
SET     A.[Field One] = B.[Field One],
        A.[Field Two] = B.[Field Two],   
        A.[Field Three] = B.[Field Three]
FROM    [table] A
CROSS JOIN (SELECT * FROM [table] WHERE [Primary Key] = 'ABS') B
Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
1
UPDATE x
SET
  x.[Field One] = y.[Field One],
  x.[Field Two] = y.[Field Two],
  x.[Field Three] = y.[Field Three]
FROM 
  <tablename> x
JOIN 
  <tablename> y
ON
  x.[Primary Key] = 'NJK' AND
  y.[Primary Key] = 'ABS'
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • What happens, if a third row is added? – HHH Oct 22 '14 at 08:33
  • @HHH I don't understand the question ? This is not a trigger. Nothing will happen when you add a third row. This query will not affect a third row because a primary key is unique(if it really is a primary key, nameing a column primary key does not make it a primary key). so it should never be affected by this query – t-clausen.dk Oct 22 '14 at 08:45
  • In his question, there is only 2 records (NJK and ABS). What if there was a third record as HHH. that will be excluded from the Update, right?. if he want to update 20 records at a time, wont it be difficult to give every condition in the ON condition? – HHH Oct 24 '14 at 09:45
  • @HHH if there is a primary key HHH it will not be affected in my answer. It will be affected in Jithin Shaji's answer though. All rows will be affected, making it a dangerous answer. – t-clausen.dk Oct 24 '14 at 10:27