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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

UPDATE table name SET column1 value1 column2 value2     WHERE some column some value    http   www w3schools com sql sql update asp

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I tried with this way and its working fine    UPDATE    Emp SET    ID   123     Name    Peter   FROM    Table Name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     amp  CDbl cur amount   amp                          totalamount to pay     amp  current total  amp                 WHERE credit id     amp  lbcreditId Text  amp          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

User · Answer

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

[sql] Update multiple columns in SQL

Examples related to sql

Examples related to sql-server

Examples related to sql-update