I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN @Accessories
WHEN 'Description' THEN @Description
WHEN 'Specification' THEN @Specification
ELSE Details
END
WHERE DeviceID = 10
The parameters are filled in by different users. The problem I face now is that even when you want to update a single field you still have to fill out with the old data the other parameters. Therefore I would like to set the option IF @parameter IS NULL THEN keep the value that is already stored in the DB. I tried to find some solution and something like the following query seems to be the solution but I can't get it to work:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' IS NOT NULL THEN @Accessories
WHEN 'Description' IS NOT NULL THEN @Description
WHEN 'Specification' IS NOT NULL THEN @Specification
ELSE Details
END
WHERE DeviceID = 10
THE DB is stored in SQL Server 2008
Thanks in advance for the help.
EDIT for clarifying:
The original table looks like this
DeviceID|Name |Details |
10 |Accessories |earplugs |
10 |Description |protectors|
10 |Specification|BeatsByDre|
For publishing reasons I made a query to turn the tables. So when the Select query is ran the return table looks like the following
DeviceID|Accessories|Description|Specification|
10 |earplugs |protectors |BeatsByDre |
I created a UI to let system users update the different fields like Accessories, Description,Specification.
The Update works if I update all fields with the query shown in the top. However when I Leave a textbox empty, then I get an error that the @parameter is missing a value. So trying to find a solution to update only the field where is something written. So if @parameter IS NULL then keep the original value in the DB.
I found another solution which is really close to what I want but can't get it to work.
Here is the other solution:
https://stackoverflow.com/questions/9387839/mysql-if-not-null-then-display-1-else-display-0
Best Answer
I think this will solve the issue:
or this (to avoid redundant updates):
or this, using a table value constructor: