How do I join tables on an update

Question

I have the following query:

select      count(L.ID)
from        LA inner join L on (LA.leadid = L.ID)
where       L.status = 5
and         L.city = "cityname"
and         Date(LA.Datetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

which looks for records with status 5 in a particular city that are older than 6 months (the date for which is stored in LA). This returns about 4k results. I would like to update the value of the status to 1 on each of those records, and so my update looks like:

update      L, LA
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

but it stalls out and locks the db. I suspect there is a problem because there is no join, but I try something like:

update      L, LA
from        L inner join LA on (L.OID = LA.leadid)
set         L.status = 1
where       L.status = 5 
and         L.syscity = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

and it obviously won't work because there is no 'from' in an update.

edit> I'm using MySQL

score 7 · Accepted Answer · answered Mar 02 '09 at 22:45

7

update      L
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         EXISTS (
  select * from LA 
  where Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)
  and LA.leadid = L.ID
)

answered Mar 02 '09 at 22:45

Steve Weet

28,126
11
70
86

score 3 · Answer 2 · answered Mar 02 '09 at 23:14

3

For MySQL, you may use old join syntax:

UPDATE  l, la
SET     l.status = 1
WHERE   l.status = 5
  AND   l.city = "cityname"
  AND   la.leadid = l.id
  AND   DATE(la.datetime) < DATE_SUB(NOW(), INTERVAL 6 MONTH)

answered Mar 02 '09 at 23:14

Quassnoi

413,100
91
616
614

This should work for any standard-compliant DBMS, including SQL Server – Alexander Lebedev Mar 02 '09 at 23:32
This won't work on Oracle. You would need to update inline view: UPDATE (SELECT * FROM L, LA WHERE ...) SET L.status = 1, and even in this case L could not be updated, as it's not key preserved in this query (this view can return one L.ROWID several times). – Quassnoi Mar 02 '09 at 23:44

score 1 · Answer 3 · edited May 23 '17 at 12:01

1

I would do this:

update L
set status = 1
from LA
where L.OID = LA.leadid
and L.status = 5
and L.syscity = "cityname"
and Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)

See also:

SQL update from one Table to another based on a ID match

edited May 23 '17 at 12:01

Community

1
1

answered Mar 02 '09 at 22:42

MikeW

5,702
1
35
43

answered before he edited to say MySQL - I probably should have guessed – MikeW Mar 03 '09 at 08:59

score 1 · Answer 4 · edited Sep 27 '11 at 16:09

1

In SQL Server 2005 this will work:

Update L
   set L.status = 1
from
   L
   --
   JOIN LA
      on (LA.leadid = L.id)
where
   L.status = 5
   and L.city = "cityname"
   and Date(LA.Datetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

edited Sep 27 '11 at 16:09

answered Mar 02 '09 at 23:02

Ron Savage

10,923
4
26
35

I use this style even for a single table - so that all UPDATE statements are consistent, and a JOINed table can easily be added later. Normally use "U" for the Alias for the table being updates: UPDATE U SET Col1 = 1 FROM MyTable1 AS U JOIN MyTable2 ON T2_ID = U.ID – Kristen Mar 02 '09 at 23:17
That's the format I use as well for all my SQL - but I use alias that are abreviations for the table name, like "i" for Item, "p" for price because I'm lazy and it's easier to remember. :-) – Ron Savage Mar 03 '09 at 00:50

How do I join tables on an update

4 Answers4