替换SQL中的值(Microsoft Access) [英] Replacing Values in SQL (Microsoft Access)

问题描述

所以:我有一张小桌子，看起来像:

So: I have a small table which looks like :

PartNumber  ReplacedNumber
408077-5102 408077-5102S
408077-0102 408077-5102

如您在此处看到的，我想用替换编号替换部件号，问题是如果a被b替换，而b被c替换，则意味着a被c替换.

As you can see here, i want to replace the Part number with a Replaced number, with the issue being if a is replaced by b, and b replaced by c, then it implies that a is replaced by c.

有人知道这样做的方法吗?

Anyone knowing a way to do this?

感谢您的帮助

更新:
好的，这是示例数据:

UPDATE:
Okay, here is the sample data:

id PartNumber   ReplacedNumber
1  408077-5102  408077-5102S
2  408077-0102  408077-5102

如您所见，第二行中的值被替换(a被b替换，在这种情况下，408077-0102被408077-5102替换)，然后b被c替换(408077-5102为在第一行中用408077-5102S替换).

As you can see, the value in the 2nd row is replaced (a is replaced by b, which in this case 408077-0102 is replaced by 408077-5102), and then b is replaced by c (408077-5102 is replaced by 408077-5102S in the 1st row).

这意味着a = c.我想避免在重复中替换值.我希望现在更加清楚.

That implies a = c. I want to avoid replacing values in repetition. I hope it is clearer now.

谢谢

推荐答案

如果a替换为b，b替换为c，则意味着a替换为c可以这样写:

If a is replaced by b, and b is replaced by c, then it implies that a is replaced by c could be written as this:

SELECT
  Replace.ID,
  Replace.PartNumber,
  IIF(Not IsNull([Replace_1].[ReplacedNumber]),
      [Replace_1].[ReplacedNumber],
      [Replace].[ReplacedNumber])
FROM
  Replace LEFT JOIN Replace AS Replace_1
    ON Replace.ReplacedNumber = Replace_1.PartNumber

(这是一个SELECT查询，但是可以很容易地在UPDATE查询中进行转换)，但这不能解决c被d替换的情况，(我想)还意味着a被d替换.

(it's a SELECT query but it could be easily transformed in a UPDATE query) but this does not solve the case of c replaced by d, that (i suppose) also implies that a is replaced by d.

我认为这里唯一的解决方案是使用递归函数:

I think the only solution here is to use a recursive function:

Function searchReplaced(ReplacedNumber) as Variant
  Dim Look As Variant

  Look = DLookup("ReplacedNumber",
                 "Replace",
                 "PartNumber=""" & ReplacedNumber & """")

  If IsNull(Look) Then
    searchReplaced = ReplacedNumber
  Else
    searchReplaced = searchReplaced(Look)
  End If

End Function

然后您就可以启动此UPDATE查询:

And then you just launch this UPDATE query:

UPDATE Replace
SET Replace.ReplacedNumber = searchReplace([Replace].[ReplacedNumber])

这篇关于替换SQL中的值(Microsoft Access)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！