Can I do a mysql Select, Update and Delete in one query?
Solution 1
You can't reduce the number of queries - they all do different things - but you could reduce the number of round trips to the database and the number of parses by wrapping it all as a PLSQL function.
However you can't select the data after you've deleted it.....but consider:
CREATE PROCEDURE s_u_d(a)
BEGIN
UPDATE tab_x SET tab_x.avalue=1 WHERE tab_x.another=a;
DELETE FROM tab_y WHERE tab_y.avalue=a;
SELECT *
FROM tab_x
WHERE tab_x.another=a;
END;
NB - you can also run multiple selects in the same procedure and handle multiple, different shaped result sets, e.g. see this page
Solution 2
NO,
only can combine
- DELETE and SELECT
- UPDATE and SELECT
This is not a proper way for mysql optimization simply
because each query come with different query cost.
And in myisam, it involve table level locking for write
Example for UPDATE and SELECT
/* this will update TABLE_A if ID in TABLE_B exist in TABLE_A */
UPDATE TABLE_A, TABLE_B
SET TABLE_A.SOME_COLUMN=TABLE_B.SOME_COLUMN
WHERE TABLE_A.ID=TABLE_B.ID
/* or */
UPDATE TABLE_A
SET SOME_COLUMN = (SELECT SOME_COLUMN_B FROM TABLE_B WHERE ... LIMIT 1)
Example for DELETE and SELECT
DELETE FROM TABLE_A WHERE TABLE_A IN(SELECT ID FROM TABLE_B)
webdev_007
Updated on June 05, 2022Comments
-
webdev_007 almost 2 years
Can I say that one of many ways to optimize mysql is to reduce the number of queries?
If that so, can I do this:
- Select "data" => $A from table X - Update $A from table Y - Delete $A from table X
in one query?