Why is UPDATE statement not working in SQL code?

Question

I am trying to update one row of the storeID column. When I do the first code below, it runs but will not affect any row. However, the bottom two are producing the following error " The UPDATE statement conflicted with the REFERENCE constraint "Products_FK". The conflict occurred in database "group7", table "dbo.Products", column 'storeID'."

Could anyone help? Thanks!

Table in SQL

UPDATE Store
SET storeID='E50'
WHERE storeID='D50'
AND storeID is Null;

UPDATE Store
SET storeID='E50'
WHERE storeID='D50'

UPDATE Store
SET storeID='E50'
WHERE storeName='A Plus Cables'

Above is the code that I have tried and nothing is being updated.

@ Ryan. No it doesn't. It is messed up in all the tables. Our professor switched around data after we had placed them in SQL. — cscs
– cscs, Commented Dec 1, 2022 at 18:40
Then that is why it is failing. The provided answer gives enough details to explain this, but as the answer states, since that column has reference to the product table and that key doesn't exist in products, it won't allow you to update it to a value that doesn't exist. — Ryan Wilson
– Ryan Wilson, Commented Dec 1, 2022 at 18:43

David · Accepted Answer · 2022-12-01 16:47:01Z

2

The first one can't possibly match any rows, because a single value can't equal two different things at the same time:

WHERE storeID='D50'
AND storeID is Null

So that one is kind of a moot point. You'd need to update the WHERE clause to target the record(s) you want to target.

For the latter two, the error is telling you what's wrong. You're trying to write this value to a column:

SET storeID='E50'

But the error is telling you:

That column is a foreign key to another table. (Products?)
The value you're writing isn't presentin that other table.

So your options are:

Use a value that is present in the other table.
Use NULL (and update the column to allow NULL if necessary).
Remove the foreign key constraint to that other table.

answered Dec 1, 2022 at 16:47

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is UPDATE statement not working in SQL code?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related