10

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

Improve this question
4
  • Can you provide this exact error? Is it an SQL-Server error or an error in your (UI) application? What UPDATE statement does the UI send to the database? Commented Mar 15, 2013 at 11:06
  • Can you add that in the question? It may be with how the statement is constructed and what quotes are used. The '@'Accessories doesn't make sense to me. Commented Mar 15, 2013 at 11:51
  • 1
    I think that your UI code may be not producing valid statements. Show us the code you have and produces the error, not pseudo-code. Commented Mar 15, 2013 at 12:30
  • I want to thank you agaim for the help. Sorry for my lack of experience. I managed to find the error, and you were right the problem was at the UI level. Thank you so much for the help. In case someone is looking for a similar answer I used the first solution Commented Mar 15, 2013 at 14:32

1 Answer 1

Reset to default
16

I think this will solve the issue:

UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN COALESCE(@Accessories, Details) WHEN 'Description' THEN COALESCE(@Description, Details) WHEN 'Specification' THEN COALESCE(@Specification, Details) ELSE Details END WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN @Accessories WHEN 'Description' THEN @Description WHEN 'Specification' THEN @Specification ELSE Details END WHERE DeviceID = 10 AND ( Name = 'Accessories' AND @Accessories IS NOT NULL OR Name = 'Description' AND @Description IS NOT NULL OR Name = 'Specification' AND @Specification IS NOT NULL ) ;

or this, using a table value constructor:

UPDATE da SET da.Details = upd.Details FROM DeviceAttribute AS da JOIN ( VALUES ('Accessories' , @Accessories), ('Description' , @Description), ('Specification' , @Specification) ) AS upd (Name, Details) ON upd.Name = da.Name WHERE da.DeviceID = 10 AND upd.Details IS NOT NULL ;
Improve this answer
5
  • Thanks for the quick answer. I will try it out and let you know of the result. Commented Mar 15, 2013 at 9:59
  • You mean the 2nd solution in my answer? Commented Mar 15, 2013 at 10:09
  • yes, I am trying the 3rd solution, I upvoted the answers because it gives lots of possibilities. I ty the 3rd and if I get it to work I will accept it also. Commented Mar 15, 2013 at 10:10
  • All should work. If they don't, you can edit your question and add the table structure (Create Table statement) and the Stored Procedure. There may be something relevant that I misunderstand or you didn't mention. Commented Mar 15, 2013 at 10:35
  • Id these answers work when all parameters are not null but do not work when one of them is null, then look at the UI code and how it handles null when producing the query. And you could add that (UI) code at the question. Commented Mar 15, 2013 at 13:03

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.