SQL更新信息使用同一表中的其他记录 [英] SQL Update info using other record from the same table

问题描述

http://imageshack.us/photo/my-images/839 /noeuds.jpg/

只需为真实表添加一个链接. Noeud 116的INSEE_COM已更新了117的信息.(116在117记录中与N_AMONT相关)

Just added a link for the real table. Noeud 116 sould have INSEE_COM updated with info on 117. (116 is related as N_AMONT in 117 record)

我有这张桌子(无提示):

I have this table (noueds):

NOEUD   TYPE_MAT  N_AMONT   LONG_CABLE   ADDRESS
123     REP       100       12           abc
130     AMP       229       12            
173     PPP       130        1           AAA

我想编写一个UPDATE查询，以填充节点号为NODES.LONG_CABLE = 1的节点之后的所有数据地址.

I would like to write an UPDATE query to fill all the data ADDRESS from node after the one that has NODES.LONG_CABLE = 1.

例如:

我搜索了所有LONG_CABLE = 1的节点，从而得到了noeud 173；那么130之前的节点应该具有相同的地址173.

I searched all the nodes that have LONG_CABLE = 1, gives me noeud 173; then the node before 130 should have the same address of 173.

我可以搜索将要更新的所有节点:

I can search all the nodes that will be updated with:

SELECT *
  FROM noeuds AS tab,
       [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)="    1"))]. AS a1
 WHERE (((tab.NOEUD)=([a1].[n_amont])) AND ((tab.ADDRESS)="     "));

我正在使用MS Access.

I'm using MS Access.

它仅更新为1m calbe选择的记录.有谁知道如何使它更新以noeuds.n_amont所指向的那些而不是在选定表中，而是在原始的称为noeuds的表上进行更新?

It is updateing only onrecords selected for 1m calbe. does anyone know how to make it update on the ones pointed by noeuds.n_amont not in the selected table but on the original one called noeuds?

推荐答案

我将其作为两个查询提供，因此您可以看到第一个查询是否返回正确的地址和代码组合以进行更新.

I am offering this as two queries so you can see if the first one is returning the right combination of address and code to update.

FirstQ

SELECT n.NOEUD, n.ADDRESS, 
     (SELECT TOP 1 Noeud 
      FROM Noeuds WHERE Noeud<n.Noeud 
      ORDER BY Noeud DESC) AS CodeToUpdate
FROM noeuds AS n
WHERE n.LONG_CABLE="1"
ORDER BY n.NOEUD

更新

UPDATE noeuds 
INNER JOIN FirstQ 
ON noeuds.NOEUD = FirstQ.CodeToUpdate 
SET noeuds.ADDRESS = [FirstQ].[ADDRESS]</s>

编辑评论

SELECT a.noeud,
       a.long_cable,
       a.address,
       noeuds_1.n_amont,
       noeuds_1.address
FROM   (SELECT *
        FROM   noeuds
        WHERE  long_cable = '1') AS a
       INNER JOIN noeuds AS noeuds_1
         ON a.n_amont = noeuds_1.noeud; 

更新#2

UPDATE (SELECT n_amont,
               address
        FROM   noeuds
        WHERE  long_cable = '1') AS a
       INNER JOIN noeuds AS n
         ON a.n_amont = n.noeud
SET    n.address = a.Address
WHERE  n.address IS NULL

这篇关于SQL更新信息使用同一表中的其他记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！