CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 609

SQL update only when non null

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.

Upvotes: 1

Views: 61

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

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

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 2

Related Questions