CODEWITHSUNDEEP
sqlsql-servert-sqljoinsql-update
Rani Radcliff
Rani Radcliff

Reputation: 5074

Update value of column in one table from a column in another table where ID's do not match

I know how to do this when the ID columns match, I do not know how to do it when they do not. I need to update the value in one row of a table in one database with the value of a column from another database. They do not have the same ID's. 

UPDATE DevDatabase
SET DevDatabase.ReportData = TESTDatabase.ReportData
Where DevDatabase.SavedReportId = '0B6D01B7-D52E-4799-B809-DA375449AF30'
FROM TESTDatabase.[SavedReport] TEST
where TEST.SavedReportId =   '02F62AD5-7DA2-414D-873E-1ED6B922DB53'

I hope the code above though incorrect demonstrates what I need.

Upvotes: 1

Views: 49

Answers (2)

GMB
GMB

Reputation: 222402

You can also use the update/join syntax, as you originally intended:

  • there should be only one WHERE clause in the query

  • the update clause should refer to a table (or alias) defined in the from clause

Query:

UPDATE d
SET ReportData = t.ReportData
FROM DevDatabase.SavedReport d
JOIN TESTDatabase.[SavedReport] t 
    ON t.SavedReportId = '02F62AD5-7DA2-414D-873E-1ED6B922DB53'
WHERE d.SavedReportId = '0B6D01B7-D52E-4799-B809-DA375449AF30'

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Using subquery:

UPDATE DevDatabase.SavedReport
SET ReportData = (SELECT ReportData 
                 FROM TESTDatabase.[SavedReport] TEST 
                 WHERE TEST.SavedReportId = '02F62AD5-7DA2-414D-873E-1ED6B922DB53')
Where SavedReportId = '0B6D01B7-D52E-4799-B809-DA375449AF30'

Upvotes: 4

Related Questions