select, delete & update from multible tables using a single query in mysql

Question

for example i use following command to find a record

SELECT `users`.`mail` 
  FROM `users` 
 WHERE `users`.`uid` = %s

if found an match, then i should like to delete this record, and update in the same query an another table. i can solve this with 'joins' ?

Answer 1

No, there's no way to do three separate operations in one query.

Why do you need to do it in one?

Answer 2

if found an match, then i should like to delete this record, and update in the same query an another table. i can solve this with 'joins' ?

Not with a single SQL query, no.

But you could perform those actions, using separate SQL queries, within a single stored procedure. This would be faster than submitting three queries separately from your application, because there's no time/performance lost transferring data back and forth over the wire (to and from your application code).

Answer 3

I think the only reason you would want to select, then delete, is if you are doing it yourself and want to make sure you are deleting the right things. Something like

DELETE users.mail FROM users WHERE users.uid = %s

will return 0 if it deleted no rows, or a number of rows it deleted. You could just check the return and make a decision based on that as to what to update...

Answer 4

If your goal is to improve performance you can do it using a single CALL to the DB using a store procedure that does 2 different queries inside.