CODEWITHSUNDEEP
sql-server
Rita Shroff
Rita Shroff

Reputation: 175

Update a column of a table with a column of another table in SQL Server

I am trying to update column from one table to another table of different database.

I searched on stack but found answers for PostgreSQL and SQLite. Those code ain't worked for me properly in SQL Server.

Say,

I want to update like 

UPDATE T1 
SET D2.T1.C1 = D1.T1.C1
WHERE D2.T1.C2 = D1.T1.C2

Everything except the where clause works fine.

Here is some code I tried:

use DB2
GO

UPDATE TABLE1 
SET COL1 = (SELECT COL1 FROM DB1.dbo.TABLE1) 
WHERE COL2 = DB1.dbo.TABLE1.COL2

How shall I write this query?

Note: D1 and D2 are identical. Both have the exact same schema. Just the name and data are different. Both databases are on the same server.

Upvotes: 0

Views: 51

Answers (3)

Donnie Nosayaba Imafidon
Donnie Nosayaba Imafidon

Reputation: 93

Or update tableName set column Name = (select columnName from second table where 1=1) i.e condition is either true or false optional though

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

You can simply use an INNER JOIN cause support Cross-Database Queries, so you can do as:

UPDATE T1
SET T1.Col1 = T2.Col1
FROM DataBase1.Schema.TableName T1 INNER JOIN DataBase2.Schema.TableName T2
ON T1.ID = T2.ID;

Please, visit and read Cross-Database Queries.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

SQL Server supports an update join syntax:

UPDATE t1
SET COL1 = t2.COL1
FROM TABLE1 D1.t1
INNER JOIN TABLE2 D2.t2
    ON t1.COL2 = t2.COL2;

Actually, your current approach might work, but you should try changing it to this:

UPDATE TABLE1 D1.t1
SET COL1 = (SELECT t2.COL1 FROM TABLE2 D2.t2 WHERE T1.COL2 = T2.COL2);

Upvotes: 2

Related Questions