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 — Kuro Neko
– Kuro Neko, Commented Aug 5, 2021 at 1: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
– user14773854, Commented Aug 5, 2021 at 7:19

Bill Karwin · Accepted Answer · 2021-08-04 23:25:46Z

1

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

answered Aug 4, 2021 at 23:25

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bill Karwin Over a year ago

Why would you need it to be in one query? Explain why this is a requirement.

user14773854 Over a year ago

It is a query to be run on a jdbc prepared statement and the number of affected rows needs to be known.

Bill Karwin Over a year ago

So run two statements. Retrieve the return value, the rows affected by each statement. Add those two numbers together.

user14773854 Over a year ago

Ok..I'll try it in the morning and tell you. Where I am it is very late.

Bill Karwin Over a year ago

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.

|

Collectives™ on Stack Overflow

MySQL delete row from at least one table using one query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related