CODEWITHSUNDEEP
mysqlsubquerysql-update
user1630799
user1630799

Reputation: 75

SQL Update, subquery returns more than one row

Timezones
---------
-AreaCode varchar
-Timezone varchar

Contacts
--------
-Phone    varchar
-Timezone varchar

Everything is populated except Timezone in the Contacts table, so I want to lookup the timezone for each phone number and update the contacts. Here's what I tried to do, but MySQL gives

Error 1242 Subquery returns more than one row

For each timezone (0,-1,-2,-3,-4,-5), I execute this update:

update contacts 
set contacts.timezone = '-1' 
where left(contacts.phone,3) = (Select timezones.areacode 
                                from timezones 
                                where timezones.timezone = '-1');

Upvotes: 1

Views: 3785

Answers (4)

Amy A
Amy A

Reputation: 131

Change where part like : ....

where left(...) in (select ...... )

Upvotes: 0

valex
valex

Reputation: 24144

Your subquery returns more than one row. Just replace "=" with "IN" to handle this issue:

update contacts 
set contacts.timezone = '-1' 
where left(contacts.phone,3) in (Select timezones.areacode 
                                from timezones 
                                where timezones.timezone = '-1');

Upvotes: 2

Gonzalo.-
Gonzalo.-

Reputation: 12672

The problem is that probably there are more than 1 row in timezones that have timezone column = '-1'

You can use a Join here

update contacts join timezones on left(contacts.phone,3) = timezones.areacode and timezones.timezone = '-1'
set contacts.timezone = '-1';

It will match the areacodes with the phones, and in that cases will update with '-1'

Upvotes: 0

D. Lambert
D. Lambert

Reputation: 1304

Try an inner join on the update, or a subquery:

update contacts 
set contacts.timezone = '-1' 
where left(contacts.phone,3) in (Select timezones.areacode 
                                from timezones 
                                where timezones.timezone = '-1');

Upvotes: 0

Related Questions