UPDATE QUERY

Question

Post reply

UPDATE QUERY

jayoub

SSCertifiable

Points: 6991
More actions
March 18, 2010 at 10:14 am

#220015

I was wondering if i can do an update query with a join query. The following is what i have:
Table1: Leas
table2: cmrecc (charges table)
connection between tables is the bldgid and leasid
the join query is
select * from leas, cmrecc where leas.bldgid=cmrecc.bldgid and leas.leasid = cmrecc.leasid and leas.bldgid = '652' and cmrecc.amount = 268.00
I need to update the leas table based on the cmrecc.amount = 268.00
Please help
Thanks
Jeff

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can do an update based upon an inner join

from your code provided it would look something like this;

UPDATE Leas

SET Leas.amount = Cmrecc.amount

FROM leas

INNER JOIN cmrecc ON leas.bldgid = cmrecc.bldgid

AND leas.leasid = cmrecc.leasid

AND leas.bldgid = '652'

AND cmrecc.amount = 268.00

this is not tested so there may be syntax errors but the structure is correct, also it wasn't clear what col you want to update so i guessed.

Also I rewrote the join to use INNER JOIN rather than the old style where joins

WayneS SSC Guru Points: 95460 More actions · Answer 2

steveb. (3/18/2010)
you can do an update based upon an inner join
from your code provided it would look something like this;
UPDATE Leas
SET Leas.amount = Cmrecc.amount
FROM leas
INNER JOIN cmrecc ON leas.bldgid = cmrecc.bldgid
AND leas.leasid = cmrecc.leasid
AND leas.bldgid = '652'
AND cmrecc.amount = 268.00
this is not tested so there may be syntax errors but the structure is correct, also it wasn't clear what col you want to update so i guessed.
Also I rewrote the join to use INNER JOIN rather than the old style where joins

One important note about this that Steve didn't mention: when using the UPDATE statement with a FROM clause, the FIRST table in the FROM clause needs to be the table being updated. If not, then one day when least expected, this update will go from taking a few ms to taking hours, and dragging the whole server down.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 3

WayneS (3/18/2010)
...the FIRST table in the FROM clause needs to be the table being updated.

That is not the case as far as I know Wayne 🙂

The order of the listed tables does not matter, but it is important that SQL Server can unambiguously assign one value per row. That means that the JOIN should return exactly one row for each row of the table to be updated. If multiple rows are returned, which value ends up being assigned is not defined.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi