update query

[Ben]

Hi all,

I need to write an update query but not sure how. I can create four
update queries to do the job, but I wish to know if there is a way to do
it all in one swoop.

Currently, I have:

update tblX AS X
Set X.ProductCode = "123"
Where (X.Mfr = "A") AND (X.ProductCode = "123_")

update tblX AS X
Set X.ProductCode = "234"
Where (X.Mfr = "A") AND (X.ProductCode = "234_")

update tblX AS X
Set X.ProductCode = "123"
Where (X.Mfr = "B") AND (X.ProductCode = "123.")

update tblX AS X
Set X.ProductCode = "234"
Where (X.Mfr = "B") AND (X.ProductCode = "234.")


What is desired whether I can combine all four into one. Can you share
with me if this is possible?

Thanks in advance,

Ben

 

[Dirk Goldgar]

Hi all,

I need to write an update query but not sure how. I can create four
update queries to do the job, but I wish to know if there is a way to do
it all in one swoop.

Currently, I have:

update tblX AS X
Set X.ProductCode = "123"
Where (X.Mfr = "A") AND (X.ProductCode = "123_")

update tblX AS X
Set X.ProductCode = "234"
Where (X.Mfr = "A") AND (X.ProductCode = "234_")

update tblX AS X
Set X.ProductCode = "123"
Where (X.Mfr = "B") AND (X.ProductCode = "123.")

update tblX AS X
Set X.ProductCode = "234"
Where (X.Mfr = "B") AND (X.ProductCode = "234.")


What is desired whether I can combine all four into one. Can you share
with me if this is possible?

It's a bit clunky, but you could do something like this (untested):

UPDATE tblX SET ProductCode =
IIf(Mfr = "A" AND ProductCode = "123_", "123",
IIf(Mfr = "A" AND ProductCode = "234_", "234",
IIf(Mfr = "B" AND ProductCode = "123.", "123",
IIf(Mfr = "B" AND ProductCode = "234.", "234",
ProductCode))))
WHERE (Mfr = "A" AND ProductCode = "123_")
OR (Mfr = "A" AND ProductCode = "234_")
OR (Mfr = "B" AND ProductCode = "123.")
OR (Mfr = "B" AND ProductCode = "234.")