user3205479 user3205479 - 2 months ago 8
MySQL Question

Delete, Update with derived tables?

I have just studied

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

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


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

Example
DELETE FROM (SELECT * FROM usrs) as u WHERE u.name = '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 u.name = 'john' at line

UPDATE (SELECT * FROM usrs) as u SET u.lname ='smith' WHERE u.name = '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

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:

DELETE usrs
FROM usrs
    INNER JOIN (
        SELECT * FROM usrs WHERE name = 'john'
    ) t ON usrs.Id = t.Id

Or you could use IN:

DELETE usrs
WHERE ID IN (
   SELECT ID
   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
Comments