SQL update only when non null

Question

UPDATE main
SET main.PCH_2YR =
(SELECT TOP 1 sub.PCH_2YR FROM [dbGlobalPricingMatrix].[dbo].[tblPCHLookup_Test] sub WHERE
sub.capid = main.capid AND
sub.milespa = main.mileage AND
sub.maintained = main.maintenance AND sub.pch_2yr IS NOT NULL)
FROM [dbWebsiteLO3-PRICING]..Vehicles_Staging_Data main

I only want to set the value if the subquery returns a value that isn't NULL. How can I do that?

Only one row will ever match the conditions, but I only want to use it's value if it's not NULL.

Don't put the subquery in the SET clause, put it in FROM and add the proper filter in WHERE. Notice that the WHERE of the subquery is essentially a JOIN - 3 of the 4 conditions simply join main and sub. BTW TOP 1 without an ORDER BY actually means pick one row at random. Is that what you want? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 10, 2016 at 10:07
I didn't use ORDER BY as only one row will ever match the conditions, but I only want to use it's value if it's not NULL. — Ben
– Ben, Commented Mar 10, 2016 at 10:11

DhruvJoshi · Accepted Answer · 2016-03-10 10:19:25Z

2

Try the query like below:

UPDATE main
SET main.PCH_2YR =sub.PCH_2YR
FROM [dbWebsiteLO3-PRICING]..Vehicles_Staging_Data main LEFT JOIN
[dbGlobalPricingMatrix].[dbo].[tblPCHLookup_Test] sub ON 
sub.capid = main.capid AND
sub.milespa = main.mileage AND
sub.maintained = main.maintenance AND sub.pch_2yr IS NOT NULL
WHERE sub.PCH_2YR is NOT NULL

edited Mar 10, 2016 at 10:19

answered Mar 10, 2016 at 10:11

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

Comments

Pradeep Kumar · Accepted Answer · 2016-03-10 10:22:20Z

0

You could just do an INNER JOIN on the table instead of sub query. INNER JOINs filter out records that don't have a match in the other table.

UPDATE main
SET main.PCH_2YR = sub.PCH_2YR
FROM [dbWebsiteLO3-PRICING]..Vehicles_Staging_Data main
INNER JOIN [dbGlobalPricingMatrix].[dbo].[tblPCHLookup_Test] sub 
        ON sub.capid = main.capid 
        AND sub.milespa = main.mileage 
        AND sub.maintained = main.maintenance 
        AND sub.pch_2yr IS NOT NULL

edited Mar 10, 2016 at 10:22

answered Mar 10, 2016 at 10:12

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL update only when non null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related