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

Question

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.

Lukasz Szozda · Accepted Answer · 2020-03-11 21:02:43Z

4

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'

answered Mar 11, 2020 at 21:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rani Radcliff Over a year ago

Thank you very much!

GMB · Accepted Answer · 2020-03-11 21:13:52Z

1

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'

answered Mar 11, 2020 at 21:13

GMB

224k25 gold badges102 silver badges151 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related