CODEWITHSUNDEEP
sqlms-access
Kristia Prokopiou
Kristia Prokopiou

Reputation: 39

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

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?

Thanks for your help

UPDATE:
Okay, here is the sample data: 

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

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).

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

Thanks

Upvotes: 3

Views: 2648

Answers (2)

fthiella
fthiella

Reputation: 49049

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

(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

And then you just launch this UPDATE query:

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

Upvotes: 1

SRIRAM
SRIRAM

Reputation: 1888

If that table has primary id, you can just update the table

update table set partnumber=replacednumber where id=id of the column you want to replace

id refers to primary key

Upvotes: 0

Related Questions