mysql - MS Access SQL 更新表 A 和其他 2 个表的信息

Question

我有 3 个表:

CLIENTS
```````
num_sect
nbr
address
city

IMPORTED
````````
nbr
address
city
sector
part

SECTOR
``````
num_sect
city
sector
part

我应该对客户表进行扇区化，但只能使用与导入地址匹配的扇区导入地址。

更新表 clients.num_sect 里面的 IMPORTED 和

num_sect from sector.

我使用此选择来匹配我应该更新内部客户端的所有行。

SELECT 
    c.num_sect, 
    c.nbr, 
    c.address, 
    c.num_abo, 
    imp.address, 
    imp.sector, 
    imp.part
FROM 
    clientes AS cli, imported as imp
WHERE 
    (c.address = imp.address and c.city = imp.city and c.nbr = imp.nbr)

但是，我无法根据客户端之间的选择/关系来更新客户端的 num_sect。

谢谢

解决方案:

更新(SELECT ab.num_sect, ab.city, ab.nbr, ab.address, ab.num_abo, aps.address, aps.part & aps.sector AS CD, aps.nbr FROM clinets AS ab, imported AS aps WHERE (aps .address = ab.address and aps.city = ab.city and ab.nbr = aps.nbr)) AS bla

INNER JOIN sector ON bla.CD=(sector.part & secteur.sector & right(sector.sector,1))

SET ab.num_sect = sector.num_sect;

我在用 cint (text = text & cint (text)) 比较和连接文本时遇到了问题，似乎它不认为连接文本 & cint(nbr) 是文本。我改变了方法，但现在如果该扇区只有 1 个数字，它就可以工作(目前，这对我来说没问题)。

Answer 1

我会这样尝试:

UPDATE (SELECT ab.num_sect, ab.city, ab.nbr, ab.address, ab.num_abo, aps.address, 
               aps.part & aps.sector AS CD, aps.nbr 
        FROM clinets AS ab, imported AS aps 
WHERE (aps.address = ab.address 
       and aps.city = ab.city 
       and ab.nbr = aps.nbr)) AS bla
INNER JOIN sector ON bla.CD=(sector.part & secteur.sector & right(sector.sector,1)) 
SET ab.num_sect = sector.num_sect;