[sql] Update multiple columns in SQL

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 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

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

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

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

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.

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

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)

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.

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:

   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

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

update T1
set T1.COST2=T1.TOT_COST+2.000,
T1.COST3=T1.TOT_COST+2.000,
T1.COST4=T1.TOT_COST+2.000,
T1.COST5=T1.TOT_COST+2.000,
T1.COST6=T1.TOT_COST+2.000,
T1.COST7=T1.TOT_COST+2.000,
T1.COST8=T1.TOT_COST+2.000,
T1.COST9=T1.TOT_COST+2.000,
T1.COST10=T1.TOT_COST+2.000,
T1.COST11=T1.TOT_COST+2.000,
T1.COST12=T1.TOT_COST+2.000,
T1.COST13=T1.TOT_COST+2.000
from DBRMAST T1 
inner join DBRMAST t2 on t2.CODE=T1.CODE

I tried with this way and its working fine :

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