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.
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.
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
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
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)
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
I tried with this way and its working fine :
UPDATE
Emp
SET
ID = 123,
Name = 'Peter'
FROM
Table_Name
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
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
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
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 table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
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.
