title: How do I join tables on an update
body: <p>I have the following query:</p>
  
  <pre><code>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) &lt; Date_Sub(Now(), INTERVAL 6 MONTH);
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>update      L, LA
  set         L.status = 1
  where       L.status = 5 
  and         L.city = "cityname" 
  and         Date(LA.SomeDatetime) &lt; Date_Sub(Now(), INTERVAL 6 MONTH);
  </code></pre>
  
  <p>but it stalls out and locks the db. I suspect there is a problem because there is no join, but I try something like:</p>
  
  <pre><code>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) &lt; Date_Sub(Now(), INTERVAL 6 MONTH);
  </code></pre>
  
  <p>and it obviously won't work because there is no 'from' in an update.</p>
  
  <p>edit> I'm using MySQL</p>