Update table from select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update table from select

Author

Topic

sanchoniathon
Starting Member

2 Posts

Posted - 2014-12-12 : 10:44:10

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 !

sanchoniathon
Starting Member

2 Posts

Posted - 2014-12-12 : 13:05:54

Hello to everyone, I received the following suggestion from another forum and will test it:

First i will check if the logic works
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 will actually 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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources