mysql update rows of column in second table from column in first table where another column in first table matches column in second table

Question

My title may be a little confusing, but this is basically what I want to do:

I have two tables:

Table 1 = Site

Columns: SiteID    SiteName    Address
           1       Germany     123 A Street
           2       Poland      234 B Street
           3       France      354 F Street
           4       England     643 C Street
           5       Russia      968 G Street

Table 2 = Site_New

Columns: SiteID    SiteName    Address
           1       Germany
           2       France
           3       Russia

I wan't to update the Address column in table 2 with the Address in table 1 where SiteName in table 2 = SiteName in table 1. As you can see there are sites in table 1 that are not in table 2, so I do not care about copying those addresses to table 2.

I was trying this code:

update Site_New set Address = (select Site.Address from Site where Site_New.SiteName=Site.SiteName)

but I was getting error code 1242: "Subquery returns more than 1 row."

Any idea on how this can be done?

score 1 · Accepted Answer · answered Aug 11 '14 at 19:56

1

You are better off using update/join syntax:

update Site_New sn join
       Site s
       on sn.SiteName = s.SiteName
    set sn.Address = s.Address;

However, based on your sample data, your correlated subquery should not cause such an error.

Perhaps the join should be on SiteId rather than SiteName:

update Site_New sn join
       Site s
       on sn.SiteId = s.SiteId
    set sn.Address = s.Address;

answered Aug 11 '14 at 19:56

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon! You're first sql statement was exactly what I was looking for. Joining on Site ID would not have worked for me as the SiteID in table 1 differed from table 2. I should of showed that in my example, but I was just thinking of joining by SiteName, which turned out to work perfectly. Thanks again! And thank you all for the quick answers! – Braden Aug 11 '14 at 20:08

score 0 · Answer 2 · answered Aug 11 '14 at 19:54

you need to do a select with your update like so

UPDATE site_new sn,
(   SELECT 
        sn1.address as _address, sn1.sitename as _sitename
    FROM site_new sn1
    JOIN site s on s.sitename = sn1.sitename
) t
SET sn.address = t._address 
WHERE sn.sitename = t._sitename

mysql update rows of column in second table from column in first table where another column in first table matches column in second table

2 Answers2