UPDATE without listing all columns?

Question

Post reply

UPDATE without listing all columns?

boeledi

Ten Centuries

Points: 1151
More actions
September 21, 2011 at 9:08 am

#390448

Hi,
I have 2 tables with the very same LONG structure (table_1 and table_2).
I was wondering if it would be possible to update table_1 with the content of table_2 without specifying the columns via a very long SET statement.
In other words:
Normal way of working: UPDATE table_1 A SET A.col1 = B.col1, A.col2 = B.col2, .... INNER JOIN table_2 B ON A.col1 = B.col1
Since the list of columns in the structure is quite large, I would like to have something similar to the INSERT statement
INSERT INTO table_1 A (col1, col2, col3, col4, ....)
SELECT B.col1, B.col2, B.col3... FROM table_2 B
would become:
UPDATE table_1 (col1, col2, ....)
SELECT B.col1, B.col2, ....
Is this possible?
Many thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 1

SET statement is mandatory part of UPDATE statement (check BOL for UPDATE), therefore you can not avoid using it.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

boeledi (9/21/2011)
Hi,
I have 2 tables with the very same LONG structure (table_1 and table_2).
I was wondering if it would be possible to update table_1 with the content of table_2 without specifying the columns via a very long SET statement.
In other words:
Normal way of working: UPDATE table_1 A SET A.col1 = B.col1, A.col2 = B.col2, .... INNER JOIN table_2 B ON A.col1 = B.col1
Since the list of columns in the structure is quite large, I would like to have something similar to the INSERT statement
INSERT INTO table_1 A (col1, col2, col3, col4, ....)
SELECT B.col1, B.col2, B.col3... FROM table_2 B
would become:
UPDATE table_1 (col1, col2, ....)
SELECT B.col1, B.col2, ....
Is this possible?
Many thanks

Are you updating ALL the columns in the target table or just a lot of them? In that same breath... is there a PK associated with these two tables to make a a join easier?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 3

if you update all columns from table2 to table1 u can use merge,(for more u can check BOL) or if u want to update a set of columns then u need to mention it in ur update statement.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I tend to agree with Celko on this one. This is a prime candidate for a redesign.

That said, what are the differences between the two tables? How many records in each?

Are all records in table1 definitely to be overwritten by those in table2?

Do both tables have the same number of records and the same number of columns and the same column names?

If so, it really almost seems like you should rename the two tables.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

HowardW One Orange Chip Points: 29109 More actions · Answer 5

CELKO (9/21/2011)
However, this sounds like it might as candidate for a better schema design. Why would you ever have the same data in two places when the whole purpose of any DB is to re educe ot remove redundancies?

An OLAP database that merges data from load tables?

Merge definitely has better syntax for this, but at the end of the day, you still need to explicitly list the columns.

If you're doing this for a lot of tables (and Celko's right in that if this is OLTP, something is wrong with your design), then you could always build statements from the metadata views (sys.columns or INFORMATION_SCHEMA.columns)

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

I agree that this table should probably be re-designed.

However, you can achieve what you're after by using a trigger INSTEAD OF INSERT, that could turn INSERTs into UPSERTs using the MERGE statement.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Apparently the OP has left the building. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Jeff Moden (9/24/2011)
Apparently the OP has left the building. 😉

Elvis? 🙂

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Heh... yeah... "Dat's da ticket". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yubo1 SSChasing Mays Points: 612 More actions · Answer 10

You can use sql-code below instead of update

truncate table table_1

&

INSERT INTO table_1 A (col1, col2, col3, col4, ....)

SELECT B.col1, B.col2, B.col3... FROM table_2 B

yubo1 SSChasing Mays Points: 612 More actions · Answer 11

You can use sql-code below instead of update

truncate table table_1

&

INSERT INTO table_1 A (col1, col2, col3, col4, ....)

SELECT B.col1, B.col2, B.col3... FROM table_2 B

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

yubo1 (9/27/2011)
You can use sql-code below instead of update
truncate table table_1
&
INSERT INTO table_1 A (col1, col2, col3, col4, ....)
SELECT B.col1, B.col2, B.col3... FROM table_2 B

The problem is that the OP wants to avoid using all of the column names to do such a thing on wide tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

boeledi Ten Centuries Points: 1151 More actions · Answer 13

boeledi

Ten Centuries

Points: 1151

September 27, 2011 at 12:54 pm

#1387425

I am still there but as an observer 😉

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

boeledi (9/27/2011)
I am still there but as an observer 😉

So, any comments?

-- Gianluca Sartori