Updating rows based on one single row selected without DECLAREing variables

Question

How to update rows based on one single row.

I want to update them:

SELECT field_one, field_two, field_three FROM some_table WHERE user_ID = 296 

With values ​​in this SELECT:

  SELECT TOP 1 * field_one, field_two, field_three FROM some_table WHERE user_ID = 500 ORDER BY ID 

Currently I am only updating field_one using:

 DECLARE @field_one nvarchar(1000) SELECT @field_one = field_one FROM some_table WHERE user_ID = @copy_user_ID UPDATE some_table set field_one = @field_one where user_ID = @user_ID 

Is there a way to do this with each field without the need for DECLARE of all variables?

Answer 1

Let's see, in FoxPro you can use SCATTER and GATHER :-)

But here you can do this:

 UPDATE Table1 SET Field_one = a.Field_one, Field_two = a.Field_two, Field_three = a.Field_three FROM (SELECT TOP 1 field_one, field_two, field_three FROM some_table WHERE user_ID = 500 ORDER BY ID) a WHERE user_ID = 296 

For more advice. You can get a list of all table fields separated by commas in SSMS by opening the Object Explorer panel showing the table, then click the plus sign (+) to display the folders below the table. Then simply click on the Columns folder and drag it into the query area.