SQL Server: Update a table using SELECT

1153090 pts.
Tags:
SQL Server
I understand that in SQL Server, it's actually possible to insert into a table using the SELECT statement. But would it be possible to actually update via SELECT? We have a table that contains values and we need to update another table with those exact same values. Thanks.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this

UPDATE tableb b SET b.colb2 = (SELECT COALESCE(a.cola2,b.colb2)
FROM tablea a WHERE b.colb1 = a.cola1)                         

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Are you asking about using a SELECT, or is it about "using only a SELECT"?

    We have a table that contains values and we need to update another table with those exact same values.

    If it's about using only a SELECT, that doesn't seem to have anything to do with the question. Can you clarify why it was included?
    36,420 pointsBadges:
    report
  • danieljones

    I think this is what you are asking about

    UPDATE table1 a , table2 b

    SET a.columnname = 'some value'

    WHERE b.columnname IS NULL ;

    2,840 pointsBadges:
    report
  • ToddN2000
    A SELECT cannot do an insert or update by itself. There are times you may need to use a more elaborate statement. Can you elaborate more on what you are trying to do?
    135,495 pointsBadges:
    report
  • edwinjhoffer

    This has the benefit that it is easy to run the SELECT statement on its own first to sanity check the results but it does requires you to alias the columns as above if they are named the same in source and target tables.

    This also has the same limitation as the proprietary UPDATE ... FROM syntax shown in four of the other answers. If the source table is on the many side of a one to many join then it is undeterministic which of the possible matching joined records will be used in the Update (An issue that MERGEavoids by raising an error if there is an attempt to update the same row more than once).

    3,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: