title: Delete, Update with derived tables?
body: <p>I have just studied <code>FROM</code> clause and <code>derived tables</code> in mysql and most of the websites provided the examples using <code>SELECT</code> command</p>
  
  <p>Example <code>SELECT * FROM (SELECT * FROM usrs) as u WHERE u.name = 'john'</code></p>
  
  <p>But when I have tried  using delete or update command it does not seem to work.</p>
  
  <p>Example <code>DELETE FROM  (SELECT * FROM usrs) as u WHERE u.name = 'john'</code></p>
  
  <p><strong>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near (SELECT * FROM usrs) as u WHERE u.name = 'john' at line</strong></p>
  
  <p><code>UPDATE (SELECT * FROM usrs) as u SET u.lname ='smith' WHERE u.name = 'john'</code></p>
  
  <p><strong>1288 The target table e of the UPDATE is not updatable</strong></p>
  
  <p>So derived tables does not work with delete or update commands? or is there a way to make it work.</p>
  
  <p>Instead of writing the table name for update and delete I want to write a subquery that gets the records and perform the delete operation on that records? Is that possible in mysql?</p>
  
  <p><strong>UPDATED</strong> I have to delete a record and i have three tables, the record may exist in any of the table</p>
  
  <p>My approach <code>delete from first table rows effected? quit: else check second table rows effected? quit : else check third table</code></p>
  
  <p>But if I use <strong>UNION ALL</strong> I can do this way</p>
  
  <p><code>Delete from (select * from tb1 union all select * from tb2 union all select * from tb3) e as e.uname = 'john'</code></p>
  
  <p>but this query does not seem to work , now could anyone tell me how do i delete or update a record when i have more than one table to search. Any help is greatly appreciated. </p>