UPDATE a table with 25 columns via SELECT?

Question

In SQL Server, How can I UPDATE a table via SELECT if I have two tables each one has (25) columns?

The query is:

UPDATE
    Table1
SET
    Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2,
    ...
    ...
    ...
    Table1.col25 = Table2.col25,

FROM
    Table1
INNER JOIN
    Table2
ON
    Table1.id = Table2.id

But is there a way to update all columns without writing all 25 columns like the example above?

Answer 1

Using object explorer in SSMS, find the table in question and expand the node so you see the Columns, Keys, Constraints, Triggers, Indexes, and Statistics

Drag the Columns folder into the query window pane. This results in a column-space delimited list of the columns in a single line.

We're going to use a regular expression to transform the data into the desired format.

Things to know.

\n is a newline/carriage return/enter

^ matches the beginning of a line

. matches any thing one time

* repeats the match as long as it can go

I will tag the word we match so that we can do something with it. This comes via the braces {}. As things get tagged in braces, our reference to them by ordinal increases. Here, we'll only use one so /1 will suffice.

Regular expressions are greedy, that means it will match as much as possible which is not what we will want. Unfortunately, SSMS only supports greedy matches so we will have to make multiple passes

• How can I write a regex which matches non greedy?
• http://msdn.microsoft.com/en-us/library/ms174214.aspx

• https://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/

• Theoretical Find what: {.*?},

(note the trailing space the above)

• Theoretical Replace with: \n, T1.\1 = T2.\1

Round 1

In this step, we are going to force all of our columns to exist on a single line

Important Note: One trailing space is included in the Find what

• Find what: ,
• Replace with: \n
• Look in: Current Document
• Use: Regular expressions

Start

environment_id, environment_name, folder_id, description, created_by_sid, created_by_name, created_time

Result

environment_id
environment_name
folder_id
description
created_by_sid
created_by_name
created_time

We have now taken all of our columns and flattened them against the left margin.

Round 2

We are going to take all the words and wrap them with T1 = T2

I am going to make my output look similar to yours except I am a leading comma person.

• Find what: ^{.*}
• Replace with: , T1.\1 = T2.\1
• Look in: Current Document
• Use: Regular expressions

Start environment_id environment_name folder_id description created_by_sid created_by_name created_time

Result

,   T1.environment_id = T2.environment_id
,   T1.environment_name = T2.environment_name
,   T1.folder_id = T2.folder_id
,   T1.description = T2.description
,   T1.created_by_sid = T2.created_by_sid
,   T1.created_by_name = T2.created_by_name
,   T1.created_time = T2.created_time

Now you have a nice, almost clean column translation list. Trim the first comma and slap that into your UPDATE statement

Answer 2

By way of an example to show how you could accomplish this kind of thing, I put together the following T-SQL.

USE tempdb;
CREATE TABLE T1
(
    ID INT
    , Field1 INT
    , Field2 INT
);
CREATE TABLE T2
(
    ID INT
    , Field1 INT
    , Field2 INT
);
GO
INSERT INTO T1 (ID, Field1, Field2) VALUES (1, 2, 3);
INSERT INTO T2 (ID, Field1, Field2) VALUES (1, 4, 5);

DECLARE @TableName1 SYSNAME;
DECLARE @TableName2 SYSNAME;
DECLARE @cmd NVARCHAR(max);
SET @TableName1 = 'T1';
SET @TableName2 = 'T2';
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE ', ' END + 
    T1.TableName + '.' + T1.FieldName + ' = ' + T2.TableName + '.' + T2.FieldName
FROM (
    SELECT t1.name AS TableName, c1.name AS FieldName
    FROM sys.tables t1
        INNER JOIN sys.columns c1 ON t1.object_id = c1.object_id
    WHERE t1.name = @TableName1 AND c1.name <> 'ID'
    ) T1
INNER JOIN (
    SELECT t2.name AS TableName, c2.name AS FieldName
    FROM sys.tables t2
        INNER JOIN sys.columns c2 ON t2.object_id = c2.object_id
    WHERE t2.name = @TableName2 AND c2.name <> 'ID'
    ) T2 ON T1.FieldName = T2.FieldName

SET @cmd = 'UPDATE ' + QUOTENAME(@TableName1) + 
    ' SET ' + @cmd + 
    ' FROM ' + QUOTENAME(@TableName1) + 
    ' INNER JOIN ' + QUOTENAME(@TableName2) + 
        ' ON ' + QUOTENAME(@TableName1) + '.id = ' + QUOTENAME(@TableName2) + '.id;';

SELECT @cmd;
EXEC sp_executesql @cmd;

This requires that both tables have precisely the same structure; field names and data types must match exactly. I'm not handling schemas in the outputted dynamic SQL, so you must ensure the table names only exist in a single schema; or modify the code to handle the desired schema.

I would highly recommend against using this type of construct for several reasons. First; how much typing are you really going to save? Second; ensuring the resulting UPDATE statement is exactly what you intend is fraught with danger. Third; dynamic SQL is not exactly procedure-cache friendly. I could go on and on about why this is a bad idea.

Answer 3

just follow the following code syntex

  UPDATE Table1 SET
Table1.col1 = (SELECT col1 FROM table2 Where Table2.id=table1.id),
Table1.col2 = (SELECT col2 FROM table2 Where Table2.id=table1.id),
...
...
...
Table1.col25 = (SELECT col25 FROM table2 Where Table2.id=table1.id) FROM table1