Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://stackoverflow.com/questions/9387839/mysql-if-not-null-then-display-1-else-display-0

share|improve this question
    
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? –  ypercube Mar 15 '13 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. –  ypercube Mar 15 '13 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. –  ypercube Mar 15 '13 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 –  Greenhorn Mar 15 '13 at 14:32
add comment

1 Answer

up vote 7 down vote accepted

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 ;
share|improve this answer
    
Thanks for the quick answer. I will try it out and let you know of the result. –  Greenhorn Mar 15 '13 at 9:59
    
You mean the 2nd solution in my answer? –  ypercube Mar 15 '13 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. –  Greenhorn Mar 15 '13 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. –  ypercube Mar 15 '13 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. –  ypercube Mar 15 '13 at 13:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.