Multiple rows output into variables in MySQL

Question

The following query seems to only work when there is one single entry with dateOfBirth set to 1975-02-28.

It fails when there are multiple records matching this condition.

Is it possible to delete all the addresses of users whose attribute dateOfBirth is set to 1975-02-28 without using a subquery?

SELECT @id:=(SELECT id
             FROM USER
             WHERE dateOfBirth='1975-02-28');
DELETE FROM Address
WHERE user_id=@id;

The exact error that I get is: Error Code: 1242 Subquery returns more than 1 row..

Answer 1

The error means that your inner query is not returning a single row when you try to assign the id to a variable.

SELECT id
FROM USER
WHERE dateOfBirth='1975-02-28'

Assigning values to variable thru queries should always be single row results. If you want to store multi column results, you could do the following:

SELECT field_1, field_2, field_3 INTO @var_a, @var_b, @var_c FROM any_table

But since you asked this:

Is it possible to delete all the addresses of users whose attribute dateOfBirth is set to 1975-02-28 without using a subquery?

As told by Tonio, change your DELETE query to his suggestion and it's gonna work.

DELETE FROM Address WHERE user_id IN (SELECT id FROM USER WHERE dateOfBirth='1975-02-28');