http://imageshack.us/photo/my-images/839/noeuds.jpg/
刚刚添加了真实表格的链接。Noeud 116已经更新了INSEE_COM 117的信息。(116与117记录中的N_AMONT相关)
我有这张表(零):
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的节点之后的节点的所有数据ADDRESS。
例如:
我搜索了所有具有LONG_ABLE=1的节点,给了我noeud 173;则130之前的节点应当具有相同的地址173。
我可以搜索所有将更新的节点:
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。
编辑:
它只更新为1m calbe选择的记录。有人知道如何在noeuds.namont所指的表上更新吗?不是在选定的表中,而是在名为noeuds的原始表上?
我提供这两个查询,这样您就可以看到第一个查询是否返回了要更新的地址和代码的正确组合。
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
假设
- 节点no从1开始
- 前一个节点no始终=节点no-1
您可以执行以下操作(但不确定它是否适用于访问):
UPDATE TableName
SET spalteA = t2.SpalteB
FROM TableName AS t1
INNER JOIN TableName AS t2
ON t2.node = t1.node - 1
WHERE t1.node > 1