July 21, 2017 at 12:16 pm
I have the following tables:
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StockCurrent](
[StockItemID] [int] NOT NULL,
[LocationID] [int] NOT NULL,
[UnitCode] [varchar](5) NOT NULL,
[Quantity] [numeric](8, 4) NOT NULL,
[WAC] [numeric](20, 8) NOT NULL,
[LastReceiptCost] [numeric](20, 8) NOT NULL,
[batchNumber] [varchar](50) NOT NULL,
[expiryDate] [date] NOT NULL,
[BookStock] [int] NULL,
CONSTRAINT [PK__StockCur__0B45D852FFC50471] PRIMARY KEY CLUSTERED
(
[StockItemID] ASC,
[LocationID] ASC,
[UnitCode] ASC,
[batchNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [master].[StockItemHeader](
[StockItemID] [int] NOT NULL,
[StockItemGenericID] [int] NOT NULL,
[StockUomID] [int] NOT NULL,
[ManufacturerID] [int] NOT NULL,
[StockItemName] AS ([master].[getStockItemName]([StockItemGenericID],[stockUomID],[manufacturerID])),
[PsmasTarrifCode] [varchar](50) NULL,
[TarrifCode] [varchar](50) NULL,
[ActiveStatus] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[StockItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I want to a stored procedure to update wac in table stock current for each duplicate appearance of StockItemGenericID and StockUomID.
Some like:
update stockcurrent
set wac=''
where StockItemID IN(select StockItemID,StockItemGenericID, count(*) from master.StockItemHeader
group by StockItemID,StockItemGenericID
having count(*) > 1 .
May you kindly assist please.
Editor: Reformatted
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
July 21, 2017 at 12:33 pm
It would help to have data, but I'd reverse this and use a CTE.
WITH mycte
AS
(SELECT StockItemGenericID, StockUomID, COUNT(*) AS 'Unitcount' FROM dbo.StockItemHeader
GROUP BY StockItemGenericID, StockUomID
HAVING COUNT(*) > 1
)
, dupcte
AS
(SELECT DISTINCT StockItemID
FROM dbo.StockItemHeader sh
INNER JOIN mycte
ON mycte.StockUomID = sh.StockUomID
AND sh.StockItemGenericID = mycte.StockItemGenericID)
SELECT * FROM dbo.StockCurrent sc
INNER JOIN dupcte ON dupcte.StockItemID = sc.StockItemID
You could remove the second CTE. I added that to let you see what's happening. I assume that you are updating all values of of StockIDs in all locations. If not, you want write the final SELECT to be sure you are getting the right rows. Once you have this, then change to an update.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply