tutorial net
*********WOOOOOOW *********
We're happy to see you're enjoying our races (already 5 pages viewed today)! You can keep checking out by becoming a member of the tutorial net community. It's free!

You will also be able to keep track of your race progress, practice on exercises, and chat with other members.

Share
Go down
Admin
Posts : 200
Join date : 2017-11-11
Age : 27
Location : algeria
View user profilehttp://www.tutorial-net.com

tutorial SQL Server - How do I UPDATE from a SELECT in SQL Server?

on Wed Apr 11, 2018 5:08 pm
In SQL Server, it's possible to insert into a table using a SELECT statement:

I
Code:
NSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it also possible to update via a SELECT? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:

Code:
UPDATE Table SET col1, col2
SELECT col1, col2
FROM other_table
WHERE sql = 'cool'
WHERE Table.id = other_table.id

Answers
Code:
UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

If you are editing the the link between tables (SET Table.other_table_id = @NewValue) then change the ON statement to something like ON Table.id = @IdToEdit AND other_table.id = @NewValue – Trisped Oct 24 '12 at 18:41
Isn't this missing the WHERE clause in the question? I don't have a server on this system to test it but wouldn't you be able to add that to the ON like: ON Table.id = other_table.id AND other_table.sql='cool'? Or am I misinterpreting the question? – J V May 31 '13 at 18:15
This works by using UPDATE to iterate over the INNER JOIN. As such the ON functions as your WHERE clause and the INNER JOIN skips records that are not found in the JOINed table. Adding a WHERE clause would limit the result set of the JOINed table as well. @Roger Ray what version of MySQL and what was your query, as this DOES infact function as stated. – fyrye Sep 6 '13 at 17:14
@RogerRay, this question is about Microsoft SQL Server. Unfortunately, the syntax between various SQL implementations can vary. – Charles Wood Nov 26 '13 at 17:12
Somewhat related, I often like to write my UPDATE queries as SELECT statements first so that I can see the data that will be updated before I execute. Sebastian covers a technique for this in a recent blog post: sqlity.net/en/2867/update-from-select – dennislloydjr Aug 21 '15 at 19:48
Back to top
Permissions in this forum:
You cannot reply to topics in this forum