MySQL delete row from at least one table using one query

Question

I have two tables: one contains user logins and the other contains user data. I would like to delete users who may or may not exist in the latter table but definitely exist in the former. How do I account for users who may or may not exist? Please note that it should be in one query....
I have tried:

DELETE houses,houseusers FROM houses INNER JOIN houseusers ON houseusers.username = houses.username WHERE houses.username='user1' OR houseusers.username='user1';

It can be done in 1 query. Check this possible duplicate question [Delete from two tables in one query](https://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query/5126239) — Kuro Neko, Aug 05 '21 at 01:51
@ErrBon I don't think this is a duplicate since I am asking about deleting from at least one table if a row does not exist in the second table. The question you have linked talks about deleting from exactly two tables. — user14773854, Aug 05 '21 at 07:19

score 1 · Accepted Answer · answered Aug 04 '21 at 23:25

1

START TRANSACTION;
DELETE FROM houses WHERE username='user1';
DELETE FROM houseusers WHERE username='user1';
COMMIT;

answered Aug 04 '21 at 23:25

Bill Karwin

538,548
86
673
828

Why would you need it to be in one query? Explain why this is a requirement. – Bill Karwin Aug 04 '21 at 23:28
It is a query to be run on a jdbc prepared statement and the number of affected rows needs to be known. – user14773854 Aug 04 '21 at 23:29
So run two statements. Retrieve the return value, the rows affected by each statement. Add those two numbers together. – Bill Karwin Aug 04 '21 at 23:30
1

Ok..I'll try it in the morning and tell you. Where I am it is very late. – user14773854 Aug 04 '21 at 23:32
To be clear, I mean run two prepared statements. You can't run two SQL statements in a single call to a prepared statement. Okay, good luck tomorrow. – Bill Karwin Aug 04 '21 at 23:33
This did the trick but it would be great if it was reduced to one query. Perhaps use of 1 query is cleaner. – user14773854 Aug 05 '21 at 13:41

MySQL delete row from at least one table using one query

1 Answers1