I work on sql server 2014 I need to use inner join instead of left join
and update Haschemical i case of exist by haschemical or No in case of not exist
on statement below
i need to use inner join instead of left join because data is very big on table
ExtractReports.dbo.FinalComplanceDataDelivery may be 20 million so query take too much time to execute
update r set r.HasChemical=case when cm.partid is not null then 'HasChemical' else 'No' end
from ExtractReports.dbo.FinalComplanceDataDelivery r with(nolock)
left join
Parts.ChemicalMaster cm with(nolock) on cm.partid=r.partid
so How to do that Please ?
December 28, 2021 at 2:19 am
You would have to use two separate queries to accomplish it if you replace the LEFT JOIN.
UPDATE r
SET r.HasChemical=CASE WHEN cm.partid IS NOT NULL THEN 'HasChemical' ELSE 'No' END
FROM ExtractReports.dbo.FinalComplanceDataDelivery r
LEFT JOIN
Parts.ChemicalMaster cm ON cm.partid=r.partid;
Not sure this is any more efficient, but I'd check the query execution plans and time them to be sure.
UPDATE r
SET r.HasChemical = 'HasChemical'
FROM ExtractReports.dbo.FinalComplianceDataDelivery r
INNER JOIN Parts.ChemicalMaster cm ON cm.PartID = r.PartID;
UPDATE r
SET r.HasChemical = 'No'
FROM ExtractReports.dbo.FinalComplianceDataDelivery r
WHERE NOT EXISTS (SELECT 1 FROM Parts.ChemicalMaster pm WHERE pm.PartID = r.PartID);
I would probably just use a BIT column for this... Are there other possible answers than True/False or Yes/No?
Neither method is going to be any faster - the code is updating every row in the table ExtractReports.dbo.FinalComplanceDataDelivery. If you want to improve performance, or more likely - reduce the impact of the statement - then batching the update into smaller chunks might be an option.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply