CODEWITHSUNDEEP
mysqlsqljdbc
Riking
Riking

Reputation: 2480

How can I combine a SELECT and DELETE statement with the same conditions?

I have the two following SQL queries:

SELECT u.user FROM
mcmmo_skills AS s,
mcmmo_users AS u
WHERE s.user_id = u.id
AND (s.taming+s.mining+s.woodcutting+s.repair+s.unarmed+s.herbalism+s.excavation+s.archery+s.swords+s.axes+s.acrobatics+s.fishing) = 0;

DELETE FROM u, e, h, s, c USING mcmmo_users u
JOIN mcmmo_experience e ON (u.id = e.user_id)
JOIN mcmmo_huds h ON (u.id = h.user_id)
JOIN mcmmo_skills s ON (u.id = s.user_id)
JOIN mcmmo_cooldowns c ON (u.id = c.user_id)
WHERE (s.taming+s.mining+s.woodcutting+s.repair+s.unarmed+s.herbalism+s.excavation+s.archery+s.swords+s.axes+s.acrobatics+s.fishing) = 0;

Database schema is shown here: http://sqlfiddle.com/#!2/298cc

The purpose of these statements is to remove any users that haven't done anything, and get their usernames for some extra cleanup Java-side. As part of our movement towards PreparedStatements in JDBC, I saw this and thought it would be much cleaner if we could have a single PreparedStatement that we run, it deletes the users, and returns the userlist all in one neat package.

When I was researching this, I came across SELECT FROM OLD TABLE ( DELETE .... ) - but it appears this is only available in DB/2, and you know how it goes - can't switch.

So, how can I make my DELETE statement return the usernames in MySql?

(To say it another way, I want some SQL that deletes rows that I can put in a Statement, run execute() and have getResultSet() not return null.)

Upvotes: 0

Views: 159

Answers (1)

peterm
peterm

Reputation: 92785

So, how can I make my DELETE statement return the usernames in MySql?

Unfortunately you can't do that in MySql with one statement. What you can do though is to create a stored procedure that will return you resultset and delete afterwards.

DELIMITER $$
CREATE PROCEDURE mcmmo_select_and_delete()
BEGIN
  SELECT u.user FROM
  mcmmo_skills AS s,
  mcmmo_users AS u
  WHERE s.user_id = u.id
  AND (s.taming+s.mining+s.woodcutting+s.repair+s.unarmed+s.herbalism+
       s.excavation+s.archery+s.swords+s.axes+s.acrobatics+s.fishing) = 0;

  DELETE FROM u, e, h, s, c USING mcmmo_users u
  JOIN mcmmo_experience e ON (u.id = e.user_id)
  JOIN mcmmo_huds h ON (u.id = h.user_id)
  JOIN mcmmo_skills s ON (u.id = s.user_id)
  JOIN mcmmo_cooldowns c ON (u.id = c.user_id)
  WHERE (s.taming+s.mining+s.woodcutting+s.repair+s.unarmed+s.herbalism+
         s.excavation+s.archery+s.swords+s.axes+s.acrobatics+s.fishing) = 0;
END$$
DELIMITER ;

Upvotes: 1

Related Questions