Geek Answers Handbook

Delete, update using views?

I just learned the FROM and derived tables in mysql, and most websites have provided examples using the SELECT command

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

But when I tried to use the delete or update command, it does not work.

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

1064 - You have an error in the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax to use next (SELECT * FROM usrs) as u WHERE u.name = 'john' in the line

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

1288 Target table e UPDATE not updated

So, views don't work with delete or update commands? or is there a way to make it work.

Instead of writing the table name for updating and deleting, I want to write a subquery that receives the records and performs the delete operation on these records? Is this possible in mysql?

UPDATED I need to delete a record, and I have three tables, a record can exist in any table

My approach is delete from first table rows effected? quit: else check second table rows effected? quit : else check third table 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 it

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 someone can tell me how to delete or update the record when I have more than one table to search. Any help is appreciated.

+3
source share
2 answers

You cannot directly remove from the subquery, but you can still use it if you want, you just need to use it in the JOIN :

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

Or you can use IN :

 DELETE usrs WHERE ID IN ( SELECT ID FROM usrs WHERE name = 'John' )

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

 DELETE usrs WHERE name = 'John'

Change the comment base. To delete from multiple tables at the same time, you can have multiple DELETE , 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
+2
source

Derived tables exist only for the duration of the parent query of which they are a member. Assuming that this syntax and operations were allowed by MySQL, consider what would happen:

a) Your main request starts execution
b) the subquery executes and returns its results as a temporary table
c) parent update modifies temporary table
d) completion of the parent query e) temporary tables are cleared and deleted

Essentially, you would do nothing but waste a bunch of processor cycles and disk bandwidth.

UPDATE DO queries allow you to join other tables for use in a WHERE , for example.

 UPDATE maintable LEFT JOIN othertable ON maintable.pk = othertable.fk SET maintable.somefield='foo' WHERE othertable.otherfield = 'bar'
+1
source

More articles:


All Articles