tutorial SQL Server - How do I UPDATE from a SELECT in SQL Server?
Wed Apr 11, 2018 5:08 pm
In SQL Server, it's possible to insert into a table using a SELECT statement:
I
Answers
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
I
- Code:
NSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
- 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
Permissions in this forum:
You cannot reply to topics in this forum