user3205479 user3205479 - 1 year ago 93
MySQL Question

Delete, Update with derived tables?

I have just studied

clause and
derived tables
in mysql and most of the websites provided the examples using

SELECT * FROM (SELECT * FROM usrs) as u WHERE = 'john'

But when I have tried using delete or update command it does not seem to work.

DELETE FROM (SELECT * FROM usrs) as u WHERE = 'john'

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 = 'john' at line

UPDATE (SELECT * FROM usrs) as u SET u.lname ='smith' WHERE = 'john'

1288 The target table e of the UPDATE is not updatable

So derived tables does not work with delete or update commands? or is there a way to make it work.

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?

UPDATED I have to delete a record and i have three tables, the record may exist in any of the table

My approach
delete from first table rows effected? quit: else check second table rows effected? quit : else check third table

But if I use UNION ALL I can do this way

Delete from (select * from tb1 union all select * from tb2 union all select * from tb3) e as e.uname = 'john'

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.

Answer Source

You can't directly delete from the subquery, but you can still use it if you'd like, you'll just need to use it in a JOIN:

FROM usrs
        SELECT * FROM usrs WHERE name = 'john'
    ) t ON usrs.Id = t.Id

Or you could use IN:

   FROM usrs
   WHERE name = 'John'

With this said, for this example, I don't know why you'd want a subquery:

DELETE usrs WHERE name = 'John'

Edit base on comments. To delete from multiple tables at the same time, you can either have multiple DELETE statements, or you can use something like the following:

delete t1, t2, t3
from (select 'john' as usr) t
  left join t1 on t.usr=t1.usr
  left join t2 on t.usr=t2.usr
  left join t3 on t.usr=t3.usr
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download