Update multiple columns in SQL

Question

Is there a way to update multiple columns in SQL server the same way an insert statement is used?

Something like:

Update table1 set (a,b,c,d,e,f,g,h,i,j,k)=
(t2.a,t2.b,t2.c,t2.d,t2.e,t2.f,t2.g,t2.h,t2.i,t2.j,t2.k)
from table2 t2
where table1.id=table2.id

Or something like that, rather than like so:

update table set a=t2.a,b=t2.b etc

which can be pretty tiresome to write if you have 100+ columns.

If you're doing it programmatically, use parameterized queries and you only ever have to write it once. If you're doing it manually, use SQL Management Studio's editor and enter the data directly into the row rather than writing a query. — Dan Bechard, Aug 15 '14 at 14:51
[Unpivot the table, and then use dynamic SQL.](https://stackoverflow.com/a/60461983/4271117) — Weihui Guo, Apr 17 '20 at 13:20

score 269 · Answer 1 · edited Nov 29 '17 at 11:05

269

Try this:

UPDATE table1 
SET a = t2.a, b = t2.b, .......
FROM table2 t2
WHERE table1.id = t2.id

That should work in most SQL dialects, excluding Oracle.

And yes - it's a lot of typing - it's the way SQL does this.

edited Nov 29 '17 at 11:05

DaveyDaveDave

9,821
11
64
77

answered Jan 31 '12 at 12:40

marc_s

732,580
175
1,330
1,459

6

This won't work in Oracle: http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj26498.html – Rafał Jun 25 '14 at 10:53
18

Hi. Your'e right, but I just wanted to state it wont work in any SQL dialect. – Rafał Jun 27 '14 at 09:02
4

Works on MySQL. – João Farias Dec 16 '16 at 19:44

score 106 · Accepted Answer · answered Jan 31 '12 at 12:39

106

The "tiresome way" is standard SQL and how mainstream RDBMS do it.

With a 100+ columns, you mostly likely have a design problem... also, there are mitigating methods in client tools (eg generation UPDATE statements) or by using ORMs

answered Jan 31 '12 at 12:39

gbn

422,506
82
585
676

5

So there isn't any other way to do it in MSSQL? – Joe Jan 31 '12 at 13:14
4

@Joe: no. See answer from Alex K below(http://stackoverflow.com/a/9079904/27535), there is a request to MS to add it – gbn Jan 31 '12 at 13:22
i think use http://www.1keydata.com/sql/sqlupdate.html "SET column_1 = [value1], column_2 = [value2]" – DeLe Jun 09 '13 at 01:01
Agree re. design problem in general terms but there are circumstances where bulk validation / data cleaning may be required. I am currently engaged in so doing and in SQL Server 2012 you can now update more than 1 column per @John Woo answer below. – Hilary Aug 24 '16 at 10:59
I came here to get an answer to the poster's question, not for an opinion on design – IjonTichy Jan 13 '22 at 16:44

score 34 · Answer 3 · edited May 17 '17 at 06:54

34

Syntax

UPDATE table-name 
SET column-name = value, column-name = value, ...
WHERE condition

Example

UPDATE school
SET course = 'mysqli', teacher = 'Tanzania', student = 'you'
WHERE id = 6

edited May 17 '17 at 06:54

Francois Beaussier

558
4
13

answered Nov 13 '16 at 20:28

betrice mpalanzi

1,436
12
16

score 24 · Answer 4 · answered Jan 31 '12 at 12:42

24

Your query is nearly correct. The T-SQL for this is:

UPDATE  Table1
SET     Field1 = Table2.Field1,
        Field2 = Table2.Field2,
        other columns...
FROM    Table2
WHERE   Table1.ID = Table2.ID

answered Jan 31 '12 at 12:42

John Woo

258,903
69
498
492

1

I suspect OP just used an alias loosely because the question isn't about correctness of syntax, but "why" this syntax. Personally, I prefer using aliases throughout like I did here: http://stackoverflow.com/a/982947/27535 – gbn Jan 31 '12 at 12:54

score 24 · Answer 5 · answered Jan 31 '12 at 12:58

24

The Update table1 set (a,b,c) = (select x,y,x) syntax is an example of the use of row-value constructors, Oracle supports this, MSSQL does not. (Connect item)

answered Jan 31 '12 at 12:58

Alex K.

171,639
30
264
288

Peter · Answer 6 · 2019-05-27T10:56:22.720

11

I tried with this way and its working fine :

UPDATE 
  Emp
SET 
  ID = 123, 
  Name = 'Peter' 
FROM 
  Table_Name

edited May 27 '19 at 10:56

answered Nov 17 '15 at 14:50

Peter

663
1
7
15

2

This seems to work fine for my PostgreSQL 12.2 installation (tested using DBeaver). – Telmo Trooper Feb 26 '20 at 18:40

score 10 · Answer 7 · answered Jun 28 '13 at 10:36

10

   UPDATE t1 
    SET 
    t1.a = t2.a,
    t1.b = t2.b,
    .
    .
    .


    FROM 
    table1 t1 
    INNER JOIN table2 t2 ON  t1.id=t2.id

You can try this

answered Jun 28 '13 at 10:36

Thangamani Palanisamy

5,152
4
32
39

Just to keep in mind: Although within the "inner join" t1 and t2 could be changed, "update t2" would not work. (BTW: This answer is the simplest way to build your update statement beginning from a select statement that shows old and new values.) – Christian4145 Nov 03 '21 at 08:20

score 1 · Answer 8 · answered Feb 17 '13 at 20:40

1

here is one that works:

UPDATE  `table_1`
INNER JOIN 
 `table_2` SET  col1= value, col2= val,col3= val,col4= val;

value is the column from table_2

answered Feb 17 '13 at 20:40

Dragos Custura

121
3

score 1 · Answer 9 · answered Nov 15 '14 at 06:49

If you need to re-type this several times, you can do like I did once. Get your columns` names into rows in excel sheet (write down at the end of each column name (=) which is easy in notepad++) on the right side make a column to copy and paste your value that will correspond to the new entries at each column. Then on the right of them in an independent column put the commas as designed

Then you will have to copy your values into the middle column each time then just paste then and run

I do not know an easier solution

Weihui Guo · Answer 10 · 2020-02-29T04:42:08.223

I'd like to share with you how I address this kind of question. My case is slightly different as the result of table2 is dynamic and the column numbers may be less than that of table1. But the concept is the same.

First, get the result of table2.

Next, unpivot it.

Then write the update query using dynamic SQL. Sample code is written for testing 2 simple tables - tblA and tblB

--CREATE TABLE tblA(id int, col1 VARCHAR(25), col2 VARCHAR(25), col3 VARCHAR(25), col4 VARCHAR(25))
--CREATE TABLE tblB(id int, col1 VARCHAR(25), col2 VARCHAR(25), col3 VARCHAR(25), col4 VARCHAR(25))
--INSERT INTO tblA(id, col1, col2, col3, col4)
--VALUES(1,'A1','A2','A3','A4')
--INSERT INTO tblB(id, col1, col2, col3, col4)
--VALUES(1,'B1','B2','B3','B4')

DECLARE @id VARCHAR(10) = 1, @TSQL NVARCHAR(MAX)
DECLARE @tblPivot TABLE(    
    colName VARCHAR(255),
    val VARCHAR(255)
)

INSERT INTO @tblPivot
SELECT colName, val
FROM tblB
UNPIVOT
(
    val
    FOR colName IN (col1, col2, col3, col4)
) unpiv
WHERE id = @id

SELECT @TSQL = COALESCE(@TSQL + '''
,','') + colName + ' = ''' + val
FROM @tblPivot

SET @TSQL = N'UPDATE tblA
SET ' + @TSQL + ''' 
WHERE id = ' + @id
PRINT @TSQL
--EXEC SP_EXECUTESQL @TSQL

PRINT @TSQL result:

score -2 · Answer 11 · edited Jan 13 '17 at 03:51

-2

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

http://www.w3schools.com/sql/sql_update.asp

edited Jan 13 '17 at 03:51

Tushar

85,780
21
159
179

answered Jan 13 '17 at 00:46

Mellad Qarizada

129
1
5
16

2

This is pretty much just a repeat of the existing answers. – Pang Jan 13 '17 at 01:09

score -3 · Answer 12 · edited Jul 25 '17 at 12:48

I did this in MySql and it updated multiple columns in a single record, so try this if you are using MySql as your server:

"UPDATE creditor_tb SET credit_amount='" & CDbl(cur_amount) & "'
                   , totalamount_to_pay='" & current_total & "',   
        WHERE credit_id='" & lbcreditId.Text & "'".

However, I was coding in vb.net using MySql server, but you can take it to your favorite programming language as far as you are using MySql as your server.

Update multiple columns in SQL

12 Answers12

Linked