How do you update multiple fields from another table in mysql?

Question

This is the query I'm trying to accomplish:

update amdashboard
set (ASCID, ASCFirst, ASCLast, ASCOtherName, ASCAdd1, ASCAdd2,
     ASCCity, ASCState, ASCZip, ASCZip4, ASCY2007, ASCY2008, ASCY2009,
     ASCY2010, ASCY2011, ASCY2012, ASCEthnicity, ASCGender, ASCMaritalStatus)
= (select id, firstname, lastname, listingspousename, add1, add2,
          city, state, zip, zip4, y2007, y2008, y2009,
          y2010, y2011, y2012, Ethnicity, Gender, MaritialStatus
     from ASCNCOAClean
          inner join amdashboard
          on ASCNCOAClean.firstname = amdashboard.actorsfirst
          and ascncoaclean.lastname = amdashboard.actorslast)
    where exists (select id, firstname, lastname, listingspousename,
                         add1, add2, city, state, zip, zip4, y2007, y2008,
                         y2009, y2010, y2011, y2012, Ethnicity, Gender,
                         MaritialStatus
                    from ASCNCOAClean
                         inner join amdashboard
                         on ASCNCOAClean.firstname = amdashboard.actorsfirst
                         and ascncoaclean.lastname = amdashboard.actorslast);

I can't get this to work...receiving a syntax error on the first parenthesis. So, I figured I'd try on just one field. I tried this:

update amdashboard
set ascid = (select ascncoaclean.id
         from ASCNCOAClean 
         where ASCNCOAClean.firstname = amdashboard.actorsfirst
                           and ascncoaclean.lastname = amdashboard.actorslast)
where exists (select ascncoaclean.id
         from ASCNCOAClean 
         where ASCNCOAClean.firstname = amdashboard.actorsfirst
                           and ascncoaclean.lastname = amdashboard.actorslast);

This however returns and error 1242: Subquery returns more than 1 row. That seems silly. I know it's going to return more than one row...I want it to because I need to update multiple rows.

What am I missing?

Solution

The query you want would look something like this:

UPDATE amdashboard a, ASCNCOAClean b SET
   a.ASCID            = b.id,
   a.ASCFirst         = b.firstname,
   a.ASCLast          = b.lastname,
   a.ASCOtherName     = b.listingspousename,
   ...
   a.ASCMaritalStatus = b.MaritialStatus
WHERE a.actorsfirst = b.firstname;

Observe you will have to replace ... with the rest of the column associations I didn't write.

But be careful with that, something tells me this query is going to do something very wrong to your database, because you are not relating the tables using a unique key. If there are two records with the same ASCNCOAClean.firstname you certainly will have loss of data.

Also observe that it is going to update existing records on amdashboard, not add new ones. If your intention is to migrate data from ASCNCOAClean to amdashboard, assuming amdashboard is a brand new, empty table, then the query you want is this:

INSERT INTO amdashboard (
    ASCID, ASCFirst, ASCLast, ASCOtherName, ASCAdd1, ASCAdd2, ASCCity, ASCState, 
    ASCZip, ASCZip4, ASCY2007, ASCY2008, ASCY2009, ASCY2010, ASCY2011, ASCY2012,
    ASCEthnicity, ASCGender, ASCMaritalStatus
)
SELECT
    id, firstname, lastname, listingspousename, add1, add2, city, state,
    zip, zip4, y2007, y2008, y2009, y2010, y2011, y2012, Ethnicity, Gender,
    MaritialStatus
FROM ASCNCOAClean;

Comments

[The Hammer]

Thanks...I had come to basically that solution.

[The Hammer]

update amdashboard inner join ASCNCOAClean on ASCNCOAClean.firstname = amdashboard.actorsfirst and ascncoaclean.lastname = amdashboard.actorslast set /*amdashboard.ascid = ascncoaclean.id,*/ /*amdashboard.ascfirst = ascncoaclean.firstname,*/ amdashboard.asclast = ascncoaclean.lastname, amdashboard.ascothername = ascncoaclean.listingspousename, amdashboard.ascadd1 = ascncoaclean.add1, amdashboard.ascadd2 = ascncoaclean.add2, amdashboard.asccity = ascncoaclean.city, amdashboard.ascstate = ascncoaclean.state, amdashboard.asczip = ascncoaclean.zip ... ;

[The Hammer]

Sorry, I'm still getting used to this tool....I know that didn't format well...I tried to link on both last name and first name, and yes, I think I know I have an issue with duplicate data. This is a clean up job for a client...I wanted to update on the records that did match, then I will insert what didn't.