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?

I'm simply trying to overwrite everything I have in the old record (id=100) with whatever is in the new record (id=111). So, for instance, even without listing column names like first_name, last_name, this query will compare every column in sequence and overwrite all columns with different values. Does that make sense? — BinaryCat, Apr 28 '15 at 18:15
There is no practical way. You can generate code or do a delete-insert pair. Both are not practical in 99% of the cases. — usr, Apr 28 '15 at 18:20
Can you delete the existing row entry, and do an INSERT instead? Doing so, is relying on ordinal column positioning and is generally not best practice. — Michael G, Apr 28 '15 at 18:20
@MichaelG - But, you'd still need to list every column in order to use an INSERT, right? Or is there a way to simply insert a record by only passing the unique record id? — BinaryCat, Apr 28 '15 at 18:23
@BinaryCat, you can INSERT without listing column names. The order of the VALUES() must reflect the oridinal column position of the table you're updating. Either way, you're going to list the columns at least once. — Michael G, Apr 28 '15 at 18:23
No you don't have to list the columns in an insert at all. INSERT INTO A SELECT * FROM B would work — user2728841, Aug 22 '17 at 18:31

npe · Accepted Answer · 2015-04-28T18:32:05.590

42

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

edited Apr 28 '15 at 18:32

answered Apr 28 '15 at 18:23

npe

15,395
1
56
55

3

Yeah, I spent hours researching for a solution before posting my question here, thinking that there might be some hard-core way of getting it done that only experts know. – BinaryCat Apr 28 '15 at 18:26

score 9 · Answer 2 · answered Mar 02 '18 at 20:54

9

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.

answered Mar 02 '18 at 20:54

R.Alonso

989
1
8
9

score 3 · Answer 3 · answered Aug 27 '19 at 13:49

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)

score 2 · Answer 4 · answered Jul 10 '20 at 14:05

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.

score 1 · Answer 5 · answered Nov 07 '17 at 22:26

How about using Merge?

https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

It gives you the ability to run Insert, Update, and Delete. One other piece of advice is if you're going to be updating a large data set with indexes, and the source subset is smaller than your target but both tables are very large, move the changes to a temporary table first. I tried to merge two tables that were nearly two million rows each and 20 records took 22 minutes. Once I moved the deltas over to a temp table, it took seconds.

MERGE lets you do it all at once instead of doing a separate update, insert, and delete, but for each condition you still have to write all the SQL just as if they were separate. — E-Rock, Jul 26 '18 at 23:03

score 1 · Answer 6 · answered Jul 02 '19 at 09:07

If you are using Oracle, you can use rowtype

declare 
    var_x  TABLE_A%ROWTYPE;
Begin
    select * into var_x
    from TABLE_B where rownum = 1;

    update TABLE_A set row = var_x
    where ID = var_x.ID;
end;
/

given that TABLE_A and TABLE_B are of same schema

score 1 · Answer 7 · answered Jan 10 '20 at 04:46

It is possible. Like npe said it's not a standard practice. But if you really have to:

1. First a scalar function

CREATE FUNCTION [dte].[getCleanUpdateQuery] (@pTableName varchar(40), @pQueryFirstPart VARCHAR(200) = '', @pQueryLastPart VARCHAR(200) = '', @pIncludeCurVal BIT = 1)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @pQuery VARCHAR(8000);
WITH cte_Temp
AS
(
    SELECT
    C.name
    FROM SYS.COLUMNS AS C
        INNER JOIN SYS.TABLES AS T ON T.object_id = C.object_id
    WHERE T.name = @pTableName
)
    SELECT @pQuery = (
    CASE @pIncludeCurVal
    WHEN 0 THEN
    (
    STUFF(
        (SELECT ', ' + name + ' = ' + @pQueryFirstPart + @pQueryLastPart FROM cte_Temp FOR XML PATH('')), 1, 2, ''
        )
    )
    ELSE
    (
    STUFF(
        (SELECT ', ' + name + ' = ' + @pQueryFirstPart + name +  @pQueryLastPart FROM cte_Temp FOR XML PATH('')), 1, 2, ''
        )
    ) END)

    RETURN 'UPDATE ' + @pTableName + ' SET ' + @pQuery
END

2. Use it like this

DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery(<your table name>, <query part before current value>, <query part after current value>, <1 if current value is used. 0 if updating everything to a static value>);
EXEC (@pQuery)

Example 1: make all employees columns 'Unknown' (you need to make sure column type matches the intended value:

DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery('employee', '', 'Unknown', 0);
EXEC (@pQuery)

Example 2: Remove an undesired text qualifier (e.g. #)

DECLARE @pQuery VARCHAR(8000) = dte.getCleanUpdateQuery('employee', 'REPLACE(', ', ''#'', '''')', 1);
EXEC (@pQuery)

This query can be improved. This is just the one I saved and sometime I use. You get the idea.

score 0 · Answer 8 · answered Aug 21 '17 at 13:28

0

you could do it by deleting the column in the table and adding the column back in and adding a default value of whatever you needed it to be. then saving this will require to rebuild the table

answered Aug 21 '17 at 13:28

andy wilson

920
5
16
38

score 0 · Answer 9 · answered Nov 12 '21 at 21:03

Similar to an upsert, you could check if the item exists on the table, if so, delete it and insert it with the new values (technically updating it) but you would lose your rowid if that's something sensitive to keep in your case.

Behold, the updelsert

IF NOT EXISTS (SELECT * FROM Employee WHERE ID = @SomeID)

    INSERT INTO Employee VALUES(@SomeID, @Your, @Vals, @Here)

ELSE

    DELETE FROM Employee WHERE ID = @SomeID
    INSERT INTO Employee VALUES(@SomeID, @Your, @Vals, @Here)

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

9 Answers9

Linked