February 24, 2012 at 8:12 am
Hi,
please clarify why we use 'NOT EXIST'?
SELECT DISTINCT
Region
FROM
#Import
WHERE
CompanyImport.Region IS NOT NULL AND
NOT EXISTS
(
SELECT
'z'
FROM
Fireball.dbo.Region
WHERE
Region.Name = CompanyImport.Region
)
February 24, 2012 at 8:18 am
It's a check that only returns the distinct Regions in #Import that don't exist in Fireball.dbo.Region. E.g. This pattern would be common in the select for an INSERT into Fireball.dbo.Region, so you didn't end up inserting duplicate keys.
February 24, 2012 at 8:19 am
I don't understand if the use of EXISTS in this context is unclear to you or if it's clear but you would like a different approach.
Can you clarify please?
-- Gianluca Sartori
February 24, 2012 at 8:25 am
You can write it as follows:
SELECT DISTINCT Region
FROM #Import
WHERE CompanyImport.Region IS NOT NULL
AND CompanyImport.Region NOT IN
( SELECT 'z' FROM Fireball.dbo.Region
WHERE Region.Name = CompanyImport.Region
);
February 24, 2012 at 8:41 am
well... whoever wrote the query wanted to process rows when query below returns no rows e.g. NOT EXIST (in returned row set)
(
SELECT
'z'
FROM
Fireball.dbo.Region
WHERE
Region.Name = CompanyImport.Region
)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply