December 12, 2014 at 9:40 am
Hello to all,
I need to create a query that updates table as follows:
-- tables involved --
[Parts]
[InternationalParts]
[InternationalSuppliers]
-- Important columns --
[Parts].[Type]
[Parts].[InternationPartsID]
[Parts].[SupplierNumber]
[Parts].[SupplierName]
[Parts].[SupplierLongName]
[InternationalParts].[InternationalPartID]
[InternationalSupplier].[Name]
[InternationalSupplier].[LongName]
[InternationalSupplier].[Number]
-- Objective --
- I need to find all International Parts from the [Parts] table that are not present in the [InternationalParts]
And where the [Parts].[SuppliersName] <> [InternationalSuppliers].[Name] or [Parts].[SuppliersLongName] <> [InternationalSuppliers].[LongName]
Then we want to update the [Parts].[SuppliersName] and [Parts].[SuppliersLongName] columns WITH the values respectively from [InternationalSuppliers].[Name] and [InternationalSuppliers].[LongName]
that we found.
TIPS:to know what are the Parts from the [Parts] table which are International types:
[Part].[Type] = 1
TIPS: [Parts] and [InternationalParts] are linked with [Parts].[InternationPartsID] = [InternationalParts].[InternalPartID]
TIPS: [Parts] and [InternationalSuppliers] are linked with [Parts].[SupplierNumber] = [InternationalSupplier].[Number]
===================
SQL SOLUTION so far ...
===================
UPDATE [Parts] SET
[Parts].[SupplierName] = [InternationalSupplier].[Name] ,
[Parts].[SupplierLongName] = [InternationalSupplier].[LongName]
FROM (
SELECT [Parts].[SupplierName], [Parts].[SupplierLongName], [InternationalSupplier].[Name], [InternationalSupplier].[LongName]
from [InternationalSupplier]
INNER JOIN [Parts]
ON [InternationalSupplier].[Number] = [Part].[SupplierNumber]
WHERE [Part].[SupplierName] <> [InternationalSupplier].[ShortName] and
[Part].[SupplierName] <> [InternationalSupplier].[LongName]
)
QUESTION:
I'm not sure how to and where to code the other part of the condition which "International Parts from the [Parts] table that ARE NOT PRESENT in the [InternationalParts]"
and to know what are the Parts from the [Parts] table which are International types:
[Part].[Type] = 1
Thanks in advance for any valuable tips !
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply