如何更新给定的选择条件返回的真实值?

I want to perform an SQL update

我想执行SQL更新

UPDATE  incCustomer SET etaxCode = 'HST' WHERE

only on records where this is true.

只有在真实情况下才有记录。

select  etaxCode
from  incCustomer
left join incAddress on incCustomer.iAddressId = incAddress.iId
left join incProvince on incAddress.iProvinceStateId = incProvince.iId
where incAddress.iProvinceStateId in (     2  ,     3   ,      4   ,    6   ,   9 )

I don't think that this is possible with ANSI SQL, but can it be done in MySQL?

我认为在ANSI SQL中这是不可能的，但是在MySQL中可以实现吗?

1 个解决方案

#1

MySQL UPDATE syntax supports joins in both ANSI-89 and ANSI-92 syntax.

MySQL更新语法支持使用ANSI-89和ANSI-92语法。

Use:

使用:

   UPDATE INCCUSTOMER c
LEFT JOIN INCADDRESS a ON a.iid = c.iaddressid
LEFT JOIN INCPROVINCE p ON p.iid = a.iprovincestateid
      SET c.etaxcode = 'HST'
    WHERE a.iProvinceStateId IN (2,3,4,6,9)

I don't see the point of LEFT JOINing to the province table - I think your UPDATE could be written as:

我不认为加入省表有什么意义——我认为您的更新可以写成:

   UPDATE INCCUSTOMER 
      SET c.etaxcode = 'HST'
    WHERE EXISTS(SELECT NULL
                   FROM INCADDRESS a
                  WHERE a.iid = INCCUSTOMER.iaddressid
                    AND a.iProvinceStateId IN (2,3,4,6,9))

#1