How to UPDATE all columns of a record without havi

问题:

I'm trying to find out if there is a way to update a record without having to list every column name that is to be updated.

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

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

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

回答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.

回答3:

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.

回答4:

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