如何从mysql中的另一个表更新多个字段? [英] How do you update multiple fields from another table in 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;

这篇关于如何从mysql中的另一个表更新多个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！