Reputation: 45479
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT
statement:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it also possible to update a table with SELECT
? I have a temporary table containing the values and would like to update another table using those values. Perhaps something like this:
UPDATE Table SET col1, col2
SELECT col1, col2
FROM other_table
WHERE sql = 'cool'
WHERE Table.id = other_table.id
Upvotes: 4318
Views: 5509010
Reputation: 5161
**
BEWARE OF UPDATE WITH JOINS....with many to one relationships
When joining onto a source which has a "many" relationship to the target - This can lead to divergent and uncontrollable results! i.e. where there is more than one possible source value, it is unpredictable which will be applied to the target
**
All solution of the form of :
UPDATE ... SET ... FROM ... JOIN ...
Cna have different results according to the physical model of the database... Let's see a demo (for MS SQL Server):
1 - the tables
CREATE TABLE T_US_PRESIDENT_USP
(USP_ID INT PRIMARY KEY,
USP_FIRST_NAME VARCHAR(32) NOT NULL,
USP_SECOND_NAME VARCHAR(25));
CREATE TABLE T_US_PRESIDENT_LAST_NAMES_PLN
(PLN_ID INT PRIMARY KEY,
USP_ID INT NOT NULL REFERENCES T_US_PRESIDENT_USP (USP_ID),
USP_LAST_NAME VARCHAR(25));
INSERT INTO T_US_PRESIDENT_USP (USP_ID, USP_FIRST_NAME) VALUES
(1, 'ROOSEVELT'), (2, 'EISENHOWER'), (3, 'KENNEDY');
INSERT INTO T_US_PRESIDENT_LAST_NAMES_PLN (PLN_ID, USP_ID, USP_LAST_NAME) VALUES
(101, 1, 'Franklin'), (102, 1, 'Delano'),
(201, 2, 'Dwight'), (202, 2, 'David'),
(301, 3, 'John'), (302, 3, 'Fitzgerald');
2 - A first UPDATE JOIN query :
UPDATE T
SET USP_SECOND_NAME = USP_LAST_NAME
FROM T_US_PRESIDENT_USP AS T
JOIN T_US_PRESIDENT_LAST_NAMES_PLN AS N
ON T.USP_ID = N.USP_ID;
What does the T_US_PRESIDENT_USP table contain?
SELECT * FROM T_US_PRESIDENT_USP;
USP_ID USP_FIRST_NAME USP_SECOND_NAME
----------- ------------------- -------------------
1 ROOSEVELT Franklin
2 EISENHOWER Dwight
3 KENNEDY John
Now, adding an index to speed up the UPDATE :
CREATE INDEX X ON T_US_PRESIDENT_LAST_NAMES_PLN (USP_LAST_NAME, USP_ID);
3 - A second UPDATE JOIN query (with is just absolutly the same as 2...):
UPDATE T
SET USP_SECOND_NAME = USP_LAST_NAME
FROM T_US_PRESIDENT_USP AS T
JOIN T_US_PRESIDENT_LAST_NAMES_PLN AS N
ON T.USP_ID = N.USP_ID;
What does the T_US_PRESIDENT_USP table contain?
SELECT * FROM T_US_PRESIDENT_USP;
USP_ID USP_FIRST_NAME USP_SECOND_NAME
----------- ------------------- -------------------------
1 ROOSEVELT *Delano*
2 EISENHOWER Dwight
3 KENNEDY *Fitzgerald*
As you see the results between UPDATE 1 and UPDATE 2 has change because of the index !
Upvotes: 1
Reputation: 989
In the accepted answer, after the:
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
I would add:
OUTPUT deleted.*, inserted.*
What I usually do is putting everything in a roll backed transaction and using the "OUTPUT"
: in this way I see everything that is about to happen. When I am happy with what I see, I change the ROLLBACK
into COMMIT
.
I usually need to document what I did, so I use the "results to Text"
option when I run the roll-backed query and I save both the script and the result of the OUTPUT. (Of course this is not practical if I changed too many rows)
Upvotes: 18
Reputation: 824
UPDATE
A
SET
A.col1 = B.col1,
A.col2 = B.col2
FROM
Some_Table AS A
INNER JOIN Other_Table AS B
ON A.id = B.id
WHERE
A.col3 = 'cool'
Upvotes: 1
Reputation: 3547
Works for me well with SQLite3, update rows with SELECT after INNER SELECT.
UPDATE clients
SET col1 = '2023-02-02 18:51:30.826621'
FROM (
SELECT * FROM clients dc WHERE dc.phone NOT IN (
SELECT do.phone FROM dclient_order do WHERE do.order_date > '2023-01-01' GROUP BY do.phone
)
) NewTable
WHERE clients.phone = NewTable.phone;
Upvotes: 2
Reputation: 2838
It is important to point out, as others have, that MySQL or MariaDB use a different syntax. Also it supports a very convenient USING syntax (in contrast to T/SQL). Also INNER JOIN is synonymous with JOIN. Therefore the query in the original question would be best implemented in MySQL thusly:
UPDATE
Some_Table AS Table_A
JOIN
Other_Table AS Table_B USING(id)
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
WHERE
Table_A.col3 = 'cool'
I've not seen the a solution to the asked question in the other answers, hence my two cents. (tested on PHP 7.4.0 MariaDB 10.4.10)
Upvotes: 7
Reputation: 4484
Best practices: Update rows and save in SQL Server used in the company
WITH t AS
(
SELECT UserID, EmailAddress, Password, Gender, DOB, Location,
Active FROM Facebook.Users
)
UPDATE t SET Active = 0
It is the safest way to update the records, and this is how you can see what we are going to update. Source: URL
Upvotes: -1
Reputation: 113
Oracle SQL (use an alias):
UPDATE Table T
SET T.col1 = (SELECT OT.col1 WHERE OT.id = T.id),
T.col2 = (SELECT OT.col2 WHERE OT.id = T.id);
Upvotes: -4
Reputation: 895
Consolidating all the different approaches here.
Sample table structure is below and will update from Product_BAK to Product table.
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](100) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Product_BAK](
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](100) NULL
) ON [PRIMARY]
update P1
set Name = P2.Name
from Product P1
inner join Product_Bak P2 on p1.id = P2.id
where p1.id = 2
; With CTE as
(
select id, name from Product_Bak where id = 2
)
update P
set Name = P2.name
from product P inner join CTE P2 on P.id = P2.id
where P2.id = 2
Merge into product P1
using Product_Bak P2 on P1.id = P2.id
when matched then
update set p1.[description] = p2.[description], p1.name = P2.Name;
In this Merge statement, we can do insert if not finding a matching record in the target, but exist in the source and please find syntax:
Merge into product P1
using Product_Bak P2 on P1.id = P2.id;
when matched then
update set p1.[description] = p2.[description], p1.name = P2.Name;
WHEN NOT MATCHED THEN
insert (name, description)
values(p2.name, P2.description);
Upvotes: 52
Reputation: 444
You can use from this for update in SQL Server:
UPDATE
T1
SET
T1.col1 = T2.col1,
T1.col2 = T2.col2
FROM
Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.id = T2.id
WHERE
T1.col3 = 'cool'
Upvotes: 5
Reputation: 1640
The same solution can be written in a slightly different way as I would like to set the columns only once. I have written about both the tables. It is working in MySQL.
UPDATE Table t,
(SELECT col1, col2 FROM other_table WHERE sql = 'cool' ) o
SET t.col1 = o.col1, t.col2=o.col2
WHERE t.id = o.id
Upvotes: -2
Reputation: 61
Like this; but you must be sure to update the table and table after from have be the same.
UPDATE Table SET col1, col2
FROM table
inner join other_table Table.id = other_table.id
WHERE sql = 'cool'
Upvotes: -4
Reputation: 176
UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
[WHERE conditions];
The syntax for the UPDATE statement when updating one table with data from another table in SQL Server.
Upvotes: 10
Reputation: 1475
I was using INSERT SELECT before. For those who want to use new stuff here is a solution that works similarly, but it is much shorter:
UPDATE table1 // Table that's going to be updated.
LEFT JOIN // Type of join.
table2 AS tb2 // Second table and rename for easy.
ON
tb2.filedToMatchTables = table1.fieldToMatchTables // Fields to connect both tables.
SET
fieldFromTable1 = tb2.fieldFromTable2; // Field to be updated on table1.
field1FromTable1 = tb2.field1FromTable2, // This is in the case you need to
field1FromTable1 = tb2.field1FromTable2, // update more than one field.
field1FromTable1 = tb2.field1FromTable2; // Remember to put ; at the end.
Upvotes: 0
Reputation: 453648
Another possibility not mentioned yet is to just chuck the SELECT
statement itself into a CTE and then update the CTE.
WITH CTE
AS (SELECT T1.Col1,
T2.Col1 AS _Col1,
T1.Col2,
T2.Col2 AS _Col2
FROM T1
JOIN T2
ON T1.id = T2.id
/*Where clause added to exclude rows that are the same in both tables
Handles NULL values correctly*/
WHERE EXISTS(SELECT T1.Col1,
T1.Col2
EXCEPT
SELECT T2.Col1,
T2.Col2))
UPDATE CTE
SET Col1 = _Col1,
Col2 = _Col2;
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 MERGE
avoids by raising an error if there is an attempt to update the same row more than once).
Upvotes: 197
Reputation: 57063
In SQL Server 2008 (or newer), use MERGE
MERGE INTO YourTable T
USING other_table S
ON T.id = S.id
AND S.tsql = 'cool'
WHEN MATCHED THEN
UPDATE
SET col1 = S.col1,
col2 = S.col2;
Alternatively:
MERGE INTO YourTable T
USING (
SELECT id, col1, col2
FROM other_table
WHERE tsql = 'cool'
) S
ON T.id = S.id
WHEN MATCHED THEN
UPDATE
SET col1 = S.col1,
col2 = S.col2;
Upvotes: 894
Reputation: 8855
UPDATE YourTable
SET Col1 = OtherTable.Col1,
Col2 = OtherTable.Col2
FROM (
SELECT ID, Col1, Col2
FROM other_table) AS OtherTable
WHERE
OtherTable.ID = YourTable.ID
Upvotes: 883
Reputation: 417
Option 1: Using Inner Join:
UPDATE
A
SET
A.col1 = B.col1,
A.col2 = B.col2
FROM
Some_Table AS A
INNER JOIN Other_Table AS B
ON A.id = B.id
WHERE
A.col3 = 'cool'
Option 2: Co related Sub query
UPDATE table
SET Col1 = B.Col1,
Col2 = B.Col2
FROM (
SELECT ID, Col1, Col2
FROM other_table) B
WHERE
B.ID = table.ID
Upvotes: 10
Reputation: 1423
UPDATE from SELECT with INNER JOIN in SQL Database
Since there are too many replies of this post, which are most heavily up-voted, I thought I would provide my suggestion here too. Although the question is very interesting, I have seen in many forum sites and made a solution using INNER JOIN with screenshots.
At first, I have created a table named with schoolold and inserted few records with respect to their column names and execute it.
Then I executed SELECT command to view inserted records.
Then I created a new table named with schoolnew and similarly executed above actions on it.
Then, to view inserted records in it, I execute SELECT command.
Now, Here I want to make some changes in third and fourth row, to complete this action, I execute UPDATE command with INNER JOIN.
To view the changes I execute the SELECT command.
You can see how Third and Fourth records of table schoolold easily replaced with table schoolnew by using INNER JOIN with UPDATE statement.
Upvotes: 59
Reputation: 2798
There is even a shorter method and it might be surprising for you:
Sample data set:
CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10));
CREATE TABLE #DEST ([ID] INT, [Desc] VARCHAR(10));
INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3');
INSERT INTO #DEST VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6');
Code:
UPDATE #DEST
SET #DEST.[Desc] = #SOURCE.[Desc]
FROM #SOURCE
WHERE #DEST.[ID] = #SOURCE.[ID];
Upvotes: 25
Reputation: 135
declare @tblStudent table (id int,name varchar(300))
declare @tblMarks table (std_id int,std_name varchar(300),subject varchar(50),marks int)
insert into @tblStudent Values (1,'Abdul')
insert into @tblStudent Values(2,'Rahim')
insert into @tblMarks Values(1,'','Math',50)
insert into @tblMarks Values(1,'','History',40)
insert into @tblMarks Values(2,'','Math',30)
insert into @tblMarks Values(2,'','history',80)
select * from @tblMarks
update m
set m.std_name=s.name
from @tblMarks as m
left join @tblStudent as s on s.id=m.std_id
select * from @tblMarks
Upvotes: 1
Reputation: 3573
And if you wanted to join the table with itself (which won't happen too often):
update t1 -- just reference table alias here
set t1.somevalue = t2.somevalue
from table1 t1 -- these rows will be the targets
inner join table1 t2 -- these rows will be used as source
on .................. -- the join clause is whatever suits you
Upvotes: 52
Reputation: 1124
If you are using SQL Server you can update one table from another without specifying a join and simply link the two from the where
clause. This makes a much simpler SQL query:
UPDATE Table1
SET Table1.col1 = Table2.col1,
Table1.col2 = Table2.col2
FROM
Table2
WHERE
Table1.id = Table2.id
Upvotes: 44
Reputation: 274
The other way to update from a select statement:
UPDATE A
SET A.col = A.col,B.col1 = B.col1
FROM first_Table AS A
INNER JOIN second_Table AS B ON A.id = B.id WHERE A.col2 = 'cool'
Upvotes: 13
Reputation: 93744
Updating through CTE
is more readable than the other answers here:
;WITH cte
AS (SELECT col1,col2,id
FROM other_table
WHERE sql = 'cool')
UPDATE A
SET A.col1 = B.col1,
A.col2 = B.col2
FROM table A
INNER JOIN cte B
ON A.id = B.id
Upvotes: 50
Reputation: 1671
The below solution works for a MySQL database:
UPDATE table1 a , table2 b
SET a.columname = 'some value'
WHERE b.columnname IS NULL ;
Upvotes: 14
Reputation: 624
Use:
drop table uno
drop table dos
create table uno
(
uid int,
col1 char(1),
col2 char(2)
)
create table dos
(
did int,
col1 char(1),
col2 char(2),
[sql] char(4)
)
insert into uno(uid) values (1)
insert into uno(uid) values (2)
insert into dos values (1,'a','b',null)
insert into dos values (2,'c','d','cool')
select * from uno
select * from dos
EITHER:
update uno set col1 = (select col1 from dos where uid = did and [sql]='cool'),
col2 = (select col2 from dos where uid = did and [sql]='cool')
OR:
update uno set col1=d.col1,col2=d.col2 from uno
inner join dos d on uid=did where [sql]='cool'
select * from uno
select * from dos
If the ID column name is the same in both tables then just put the table name before the table to be updated and use an alias for the selected table, i.e.:
update uno set col1 = (select col1 from dos d where uno.[id] = d.[id] and [sql]='cool'),
col2 = (select col2 from dos d where uno.[id] = d.[id] and [sql]='cool')
Upvotes: 23
Reputation: 6215
I'd modify Robin's excellent answer to the following:
UPDATE Table
SET Table.col1 = other_table.col1,
Table.col2 = other_table.col2
FROM
Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
other_table.col1 IS NOT NULL
AND Table.col1 IS NULL
)
OR (
other_table.col2 IS NOT NULL
AND Table.col2 IS NULL
)
Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.
Upvotes: 313
Reputation: 3611
Using alias:
UPDATE t
SET t.col1 = o.col1
FROM table1 AS t
INNER JOIN
table2 AS o
ON t.id = o.id
Upvotes: 115
Reputation: 1061
UPDATE TQ
SET TQ.IsProcessed = 1, TQ.TextName = 'bla bla bla'
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0
To make sure you are updating what you want, select first
SELECT TQ.IsProcessed, 1 AS NewValue1, TQ.TextName, 'bla bla bla' AS NewValue2
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0
Upvotes: 26