Flag record if there two different locations for that record in another table 1

[scubafrog79]

I am trying to create an update statement that will update a column.

Here is the scenario. Let's say I have a bunch of products and I need to tag any of those products that exist in two different locations. Note the product can be in the same location more than once at times, I don't want to tag that product as being in a different location, since it's not.


Results would look like

Product MultiLoc
1234 Y
2345 N
4567 N
5678 Y

Hope the following scripts are helpful.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Products](
	[ProdID] [int] IDENTITY(1,1) NOT NULL,
	[MultiLoc] [int] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [Products] ON

insert into Products
	(ProdID)
values
		(1234)
	,	(2345)
	,	(4567)
	,	(5678)



SET IDENTITY_INSERT [Products] OFF

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProductLocation](
	[PK] [int] IDENTITY(1,1) NOT NULL,
	[ProdID] [int] NULL,
	[LocationID] [int] NULL,
 CONSTRAINT [PK_ProductLocation] PRIMARY KEY CLUSTERED 
(
	[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



insert into [ProductLocation]
	(ProdID, LocationID)
	values
(1234,22)
,(1234,33)
,(1234,22)
,(2345,33)
,(2345,33)
,(2345,33)
,(4567,79)
,(5678,55)
,(5678,33)
,(5678,55)

 

[Andrzejek]

Do you really need a separate table?
If you want to know multiple LocationID for the same ProdID, you can simply do:
[tt][blue]
SELECT ProdID,
COUNT(DISTINCT LocationID) AS NumOfLocations
FROM ProductLocation
GROUP BY ProdID
HAVING ( COUNT(DISTINCT LocationID) > 1 )[/blue]
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[scubafrog79]

I have no choice, the data is in two different tables. I need to mark if the product is two different locations, in the product table only

 

[Andrzejek]

If your Products table already contains all ProdID's, and my blue SELECT statement gives you the records with multiple locations, you may just simply do:

[pre]
UPDATE Products SET MultiLoc = 'N'

UPDATE Products SET MultiLoc = 'Y'
WHERE ProdID IN(
SELECT ProdID FROM ([blue]
SELECT ProdID,
COUNT(DISTINCT LocationID) AS NumOfLocations
FROM ProductLocation
GROUP BY ProdID
HAVING ( COUNT(DISTINCT LocationID) > 1)[/blue]))
[/pre]
You may combine the 2 updates into one with
[tt]...
CASE COUNT(DISTINCT LocationID) WHEN 1 THEN 'N' ELSE 'Y'
HAVING ( COUNT(DISTINCT LocationID) > 1)
...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson