CODEWITHSUNDEEP
sqlsql-server-2005sql-update
user278618
user278618

Reputation: 20262

Convert Select with Join to Update

I have a query:

SELECT t.*, tb.UpdateDate
FROM #tempBuildings tb INNER JOIN Buildings b ON b.BuildingID = tb.BuildingID 
INNER JOIN @tmp t ON t.ID = b.InvestmentId

I know it's hard to understand what is here, but in few words: I have table with buildings, and in #tempBuildings I'm counting date of update.

It's not my idea , but I must add to output one column.

So in temp table @tmp I've added this column , and I must this select query convert to update query foreach row I must do something like:

update @tmp set @tmp.UpdateDate=tb.UpdateDate

I'm sorry for luck of clarity but construction of this queries is incredible.

I'm using sql-server-2005

Upvotes: 1

Views: 1134

Answers (2)

Gratzy
Gratzy

Reputation: 9389

update t
set t.UpdateDate = tb.UpdateDate
FROM #tempBuildings tb 
INNER JOIN Buildings b ON b.BuildingID = tb.BuildingID 
INNER JOIN @tmp t ON t.ID = b.InvestmentId

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135121

doesn't this work?

UPDATE t set t.UpdateDate=tb.UpdateDate
FROM #tempBuildings tb 
INNER JOIN Buildings b ON b.BuildingID = tb.BuildingID 
INNER JOIN @tmp t ON t.ID = b.InvestmentId

Upvotes: 2

Related Questions