CODEWITHSUNDEEP
phpmysqlsqljoinsql-update
Crash Override
Crash Override

Reputation: 461

MySQL Update joining information from another table

Im really new at MySQL so please I hope you can help me since I couldn't find any related answer to my problem anywhere. Im trying to update a field in a MySQL table with information thats in another table, so far I have something like this:

UPDATE item i 
SET i.brandId = (SELECT t.taxonomyId 
                 FROM taxonomy t 
                 WHERE t.taxonomyName = (SELECT b.brandName 
                                         FROM brand b 
                                         WHERE b.brandId = i.brandId 
                                         AND b.companyId = 9) 
                 AND t.taxonomyType = 'brand' 
                 AND t.companyId = 9) 
WHERE i.companyId = 9

Basically I have an old table called brand and copied all its information to a new table called taxonomy, the only difference between both tables is the ID (everything else keeps the same), so I need to update all the brandId fields in the table item from brand to taxonomy so I can preserve the names updating the IDs.

The problem with my query is I get an error saying: Subquery returns more than 1 row

Thanks!

EDIT:

The tables structures are:

brand
brandId - brandName - companyId

item
itemId - itemName - brandId - companyId

taxonomy
taxonomyId - taxonomyName - companyId

Upvotes: 0

Views: 76

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

I think you might want this query:

update item i
   set i.brandid =
       (select t.taxonomyid
          from taxonomy t
          join brand b
            on t.taxonomyname = b.brandname
           and t.companyid = b.companyid
         where b.brandid = i.brandid)

To test before doing the update, try running the following which will illustrate what will get changed:

select t.taxonomyid   as new_id_to_be_given,
       b.brandid      as old_id_going_away,
       t.taxonomyname,
       t.companyid
  from taxonomy t
  join brand b
    on t.taxonomyname = b.brandname
   and t.companyid = b.companyid
 order by 1

This will replace the brandid on the item table with the taxonomyid value associated with the same (taxonomyname and brandname) and (companyid and companyid) values between the 2 tables.

If this query STILL returns 2+ rows, that means the TAXONOMY or BRAND table has more than one row for a unique combination of taxonomyname and companyid.

To diagnose where this might be happening you can run the following (and then after resolving the issues, you can run the above):

select 'taxonomy' as table_with_issue,
       taxonomyname,
       companyid,
       group_concat(taxonomyid separator ', ') as multiple_ids
  from taxonomy
 group by taxonomyname, companyid
having count(*) > 1
union all
select 'brand', brandname, brandid, group_concat(brandid separator ', ')
  from brand
 group by brandname, brandid
having count(*) > 1

Upvotes: 1

Christopher Pecoraro
Christopher Pecoraro

Reputation: 136

Subquery returns more than 1 row means that either of the Select statements returns more than one row.

You cannot set i.brandId to more than one value. One thing you could try would be adding limit 1:

UPDATE item i 
SET i.brandId = (SELECT t.taxonomyId 
                 FROM taxonomy t 
                 WHERE t.taxonomyName = (SELECT distinct b.brandName 
                                         FROM brand b 
                                         WHERE b.brandId = i.brandId 
                                         AND b.companyId = 9 limit 1) 
                 AND t.taxonomyType = 'brand' 
                 AND t.companyId = 9 limit 1) 
WHERE i.companyId = 9

but I would try to figure out how to add something to the where clause to make sure that you are returning only one row.

Upvotes: 0

Related Questions