How to UPDATE all columns of a record without having to list every column

Question

I'm trying to figure out a way to update a record without having to list every column name that needs to be updated.

For instance, it would be nice if I could use something similar to the following:

// the parts inside braces are what I am trying to figure out
UPDATE Employee
SET {all columns, without listing each of them} 
WITH {this record with id of '111' from other table}
WHERE employee_id = '100'

If this can be done, what would be the most straightforward/efficient way of writing such a query?

Answer 1

It's not possible.

What you're trying to do is not part of SQL specification and is not supported by any database vendor. See the specifications of SQL UPDATE statements for MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata. Every one of those supports only below syntax:

UPDATE table_reference    SET column1 = {expression} [, column2 = {expression}] ... [WHERE ...] 

Answer 2

This is not posible, but..

you can doit:

begin tran delete from table where CONDITION insert into table select * from EqualDesingTabletoTable where CONDITION commit tran 

be carefoul with identity fields.

Answer 3

Here's a hardcore way to do it with SQL SERVER. Carefully consider security and integrity before you try it, though.

This uses schema to get the names of all the columns and then puts together a big update statement to update all columns except ID column, which it uses to join the tables.

This only works for a single column key, not composites.

usage: EXEC UPDATE_ALL 'source_table','destination_table','id_column'

CREATE PROCEDURE UPDATE_ALL

@SOURCE VARCHAR(100),
@DEST VARCHAR(100),
@ID VARCHAR(100)

AS

    DECLARE @SQL VARCHAR(MAX) =  

    'UPDATE D SET ' +

    -- Google 'for xml path stuff' This gets the rows from query results and 
    -- turns into comma separated list.
    STUFF((SELECT ', D.'+ COLUMN_NAME + ' = S.' + COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @DEST
    AND COLUMN_NAME <> @ID 
    FOR XML PATH('')),1,1,'')

    + ' FROM ' + @SOURCE + ' S JOIN ' + @DEST + ' D ON S.' +  @ID + ' = D.' + @ID

    --SELECT @SQL
    EXEC (@SQL)

Answer 4

In Oracle PL/SQL, you can use the following syntax:

DECLARE
  r my_table%ROWTYPE;
BEGIN
  r.a := 1;
  r.b := 2;
  ...

  UPDATE my_table
  SET ROW = r
  WHERE id = r.id;
END;

Of course that just moves the burden from the UPDATE statement to the record construction, but you might already have fetched the record from somewhere.