12

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..

Tonio
  • 414
  • 5
  • 17
Raju
  • 249
  • 1
  • 3
  • 13
  • 1
    Could you provide more info? How does that fail? Is it a mySQL error or a program error? You talk about storing the result of this query inside an array, what programming language/environment are you using? Could you provide the exact lines that failed along with the error? – Tonio Jul 02 '14 at 14:04
  • Think "table", not "array". You are using SQL. – Gordon Linoff Jul 02 '14 at 14:05

3 Answers3

4

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');

Helio Gabrenha Jr
  • 192
  • 12
1

If there are more than one such entry, the SELECT subquery will return a table of IDs.

You cannot have an ID be equal to a table. Try this:

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

EDIT:

It seems, it is simply not possible to store multiple values inside a MySQL variable as is attempted in the following query:

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

This explanatation is mainly based on many people writing this (often qualified by the mention 'as far as I know').

I could find many such answers and no page that would go against that claim. Several examples of these answers can be found on SO (for instance: https://stackoverflow.com/a/3156565/3401018).

I tend to believe that it may very well be true. In any case, as you could see with the first part of my answer, you don't actually need to go through a variable in this particular case.

Community
  • 1
  • 1
Tonio
  • 414
  • 5
  • 17
  • For the below query SELECT @ids:=(SELECT id FROM USER WHERE dateOfBirth='1975-02-28'); I am getting the following error Error Code : 1242 Subquery returns more than 1 row. – Raju Jul 04 '14 at 06:47
  • @Raju Could you please mention whether you still have a problem or if your question is answered? And if it is, mark the answer? – Tonio Jul 07 '14 at 16:45
  • We cannot achieve with sql variables is the conclusion. So we need to solve this using subquery only. – Raju Jul 09 '14 at 06:26
  • I think that's that. What I meant however is that this is a `Question/Answer` site. If you feel satisfied with an answer, you can "mark it as answer to your question" by clicking the tick right below the votes for that answer (http://stackoverflow.com/tour). – Tonio Jul 09 '14 at 07:11
0

The problem is that subquery is returning more than one ID, if you only want 1 ID you should limit the results stating limit 1 at the end of the subquery.

On the other hand, a better solution to the problem is provided by joins :

delete a from address a join user u on (a.user_id=u.id) 
where u.dateOfBirth='1975-02-28';
DanB
  • 2,022
  • 1
  • 12
  • 24
coloro.triana
  • 1
  • 1