December 12, 2014 at 10:50 am
I would left join to [InternationalParts] and then have a where for the missing matches
December 12, 2014 at 11:08 am
Thanks for the fast reply djj. I also found out the following suggestions on another forum:
I will first try the following logic:
SELECT
Parts.SuppliersName
,InternationalSuppliers.Name
,Parts.SuppliersLongName
,InternationalSuppliers.LongName
FROM
dbo.Parts
INNER JOIN dbo.InternationalSupplier ON Parts.InternationPartsID = InternationalParts.InternalPartID
WHERE
Parts.Type = 1
AND (ISNULL(Parts.SuppliersName, '') <> ISNULL(InternationalSuppliers.Name, '') OR ISNULL(Parts.SuppliersLongName, '') <> ISNULL(InternationalSuppliers.LongName, ''))
AND NOT EXISTS (SELECT 1 FROM dbo.InternationalParts WHERE Parts.InternationPartsID = InternationalParts
Then i will actuall try the update:
UPDATE dbo.Parts
SET
SuppliersName = (CASE WHEN ISNULL(SuppliersName, '') <> ISNULL(InternationalSuppliers.Name, '') THEN ISNULL(InternationalSuppliers.Name, '') END)
,SuppliersLongName = (CASE WHEN ISNULL(Parts.SuppliersLongName, '') <> ISNULL(InternationalSuppliers.LongName, '') THEN ISNULL(InternationalSuppliers.LongName, '') END)
FROM
dbo.Parts
INNER JOIN dbo.InternationalSupplier ON Parts.InternationPartsID = InternationalParts.InternalPartID
WHERE
Parts.Type = 1
AND (ISNULL(Parts.SuppliersName, '') <> ISNULL(InternationalSuppliers.Name, '') OR ISNULL(Parts.SuppliersLongName, '') <> ISNULL(InternationalSuppliers.LongName, ''))
AND NOT EXISTS (SELECT 1 FROM dbo.InternationalParts WHERE Parts.InternationPartsID = InternationalParts.InternationalPartID)
December 16, 2014 at 2:08 pm
By the way could any of you help me make my update sql code work please as for i receive the following error message: Line 30: Incorrect syntax near ')'.
UPDATE dbo.Part
SET SupplierShortName = NationalSupplier.ShortName,
SupplierLongName = NationalSupplier.LongName
from(SELECT *
FROM dbo.Part
JOIN dbo.NationalSupplier
ON Part.SupplierNumber = NationalSupplier.Number
AND (ISNULL(Part.SupplierShortName,'') <> ISNULL(NationalSupplier.ShortName,'')
OR ISNULL(Part.SupplierLongName,'') <> ISNULL(NationalSupplier.LongName,''))
LEFT OUTER JOIN dbo.NationalPart
ON Part.NationalPartID = NationalPart.NationalPartID
WHERE Part.DWCreationEntityID = 1
AND NationalPart.NationalPartID is NULL)
December 17, 2014 at 4:44 am
sargon.le.grand (12/16/2014)
By the way could any of you help me make my update sql code work please as for i receive the following error message: Line 30: Incorrect syntax near ')'.UPDATE dbo.Part
SET SupplierShortName = NationalSupplier.ShortName,
SupplierLongName = NationalSupplier.LongName
from(SELECT *
FROM dbo.Part
JOIN dbo.NationalSupplier
ON Part.SupplierNumber = NationalSupplier.Number
AND (ISNULL(Part.SupplierShortName,'') <> ISNULL(NationalSupplier.ShortName,'')
OR ISNULL(Part.SupplierLongName,'') <> ISNULL(NationalSupplier.LongName,''))
LEFT OUTER JOIN dbo.NationalPart
ON Part.NationalPartID = NationalPart.NationalPartID
WHERE Part.DWCreationEntityID = 1
AND NationalPart.NationalPartID is NULL)
I would think that you need to have dbo.Part in the FROM. This is not exact but something like this:
UPDATE Part
SET SupplierShortName = NS.ShortName,
SupplierLongName = NS.LongName
from dbo.Part Part
INNER JOIN
(SELECT *
FROM dbo.Part
JOIN dbo.NationalSupplier ON Part.SupplierNumber = NationalSupplier.Number
AND (ISNULL(Part.SupplierShortName,'') <> ISNULL(NationalSupplier.ShortName,'')
OR ISNULL(Part.SupplierLongName,'') <> ISNULL(NationalSupplier.LongName,''))
LEFT OUTER JOIN dbo.NationalPart ON Part.NationalPartID = NationalPart.NationalPartID
WHERE Part.DWCreationEntityID = 1
AND NationalPart.NationalPartID is NULL) AS NS ON Part.xxx = NS.xxx
Also I would suggest using alias values for the two part table names.
December 17, 2014 at 2:23 pm
Thanks !
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply