patternsqlModerate
IF NOT NULL then UPDATE else keep the value of the field
Viewed 0 times
fieldtheupdateelsenullkeepvaluethennot
Problem
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
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:
THE DB is stored in SQL Server 2008
Thanks in advance for the help.
EDIT for clarifying:
The original table looks like this
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
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 o
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 = 10The 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 = 10THE 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 o
Solution
I think this will solve the issue:
or this (to avoid redundant updates):
or this, using a table value constructor:
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 ;Code Snippets
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 ;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
) ;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 ;Context
StackExchange Database Administrators Q#36747, answer score: 14
Revisions (0)
No revisions yet.