Update Only Where Value Exists

[Lorlai]

I am trying to create an update statement that will update a field only where there is a value in another field. My table structures are as such:


PRODUCT STATUS TABLE

Product Name

Product Number

Product Status

TEMP PRODUCT STATUS CHANGE TABLE

Product Name

Product Number

Product Status

Product Status Change

I want to update the PRODUCT STATUS TABLE.Product Status to the TEMP PRODUCT STATUS CHANGE TABLE.Product Status Change, but only where there actually is a product status change. This is the update query I have created:

UPDATE [TEMP PRODUCT STATUS CHANGE TABLE] RIGHT JOIN [PRODUCT STATUS TABLE] ON [TEMP PRODUCT STATUS CHANGE TABLE].[Product Number] = [PRODUCT STATUS TABLE].[Product Number] 
SET [PRODUCT STATUS TABLE].[Product Status] = [TEMP PRODUCT STATUS CHANGE TABLE].[Product Status Change]
WHERE ((([TEMP PRODUCT STATUS CHANGE TABLE].[Product Status Change]) Is Not Null));

When I run this code, the [PRODUCT STATUS TABLE].[Product Status] field is updated to the [TEMP PRODUCT STATUS CHANGE TABLE].[Product Status Change] field, but all records are updated. I need only the records that have a product status change to be updated. Is there a way to change my update query so only the status changed records become updated?


Thank you for your time and help!

[orange]

Are the records in your tables unique?

Try this, but back up your table first.

UPDATE [PRODUCT STATUS TABLE] INNER JOIN [TEMP PRODUCT STATUS CHANGE TABLE] ON 
[PRODUCT STATUS TABLE].[Product Number] = [TEMP PRODUCT STATUS CHANGE TABLE].[Product Number] 
SET [PRODUCT STATUS TABLE].[Product Status] = [TEMP PRODUCT STATUS CHANGE TABLE].[Product Status Change]
WHERE ((([TEMP PRODUCT STATUS CHANGE TABLE].[Product Status Change]) Is Not Null));

[Lorlai]

Thank you for your response Orange, and I apologize for the delay in mine.

And thank you for providing the above query. Unfortunately, I am experiencing the same difficulties with your query as I am with mine. Running your query will update the data that I need, but will also update the data that I do not want. Perhaps some example data will be a little more clear.

PRODUCT STATUS TABLE

Product name	Product Number	Product Status
Incredible 9000	12345	Green
Kirk	67890	Yellow
Tardis	111009	Red
Pond	66453	Green

TEMP PRODUCT STATUS CHANGE TABLE

Product name	Product Number	Product Status	Product Status Change
Incredible 9000	12345	Green
Kirk	67890	Yellow	Green
Tardis	111009	Red
Pond	66453	Green	Red

I need the end result to look like:
PRODUCT STATUS TABLE

Product name	Product Number	Product Status
Incredible 9000	12345	Green
Kirk	67890	Green
Tardis	111009	Red
Pond	66453	Red

Where only the product status in bold change. Currently, the two queries that have been presented change the data to look as such:
PRODUCT STATUS TABLE

Product name	Product Number	Product Status
Incredible 9000	Green
Kirk	67890	Green
Tardis	111009
Pond	66453	Red

Changing the data where it is needed, but leaving the other statuses as null.

Is there anyway to make the data appear as I need it?

Thank you!