你如何更新mysql中另一个表的多个字段?

2022-01-17 00:00:00 sql-update sql mysql

这是我要完成的查询:

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

但这会返回错误 1242:子查询返回多于 1 行.这似乎很愚蠢.我知道它会返回不止一行...我想要它，因为我需要更新多行.

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.

我错过了什么?

推荐答案

你想要的查询应该是这样的:

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.

但是要小心，有些事情告诉我这个查询会对你的数据库做一些非常错误的事情，因为你没有使用唯一键来关联表.如果有两条记录具有相同的ASCNCOAClean.firstname，你肯定会丢失数据.

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.

还要注意它将更新 amdashboard 上的现有记录，而不是添加新记录.如果您的意图是将数据从 ASCNCOAClean 迁移到 amdashboard，假设 amdashboard 是一个全新的空表，那么您想要的查询就是这个:

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;

相关文章