Update in outer Apply

Question

Update in outer Apply

CookieMonster

SSC Veteran

Points: 210
More actions
April 10, 2014 at 2:57 am

#306537

Hi All,
Is it possible to put an update query within a outer apply?
DECLARE @RemainingStock INT = 0
SELECT
OI.OrderItemId
FROM OrderItems_tbl AS OI
OUTER APPLY
(
UPDATE
StockLevel_tbl
SET
@RemainingStock = (CASE WHEN (ABS(StockLevel - OI.Quantity)) > 0 THEN (ABS(StockLevel - OI.Quantity)) ELSE OI.Quantity END),
StockLevel = (StockLevel - OI.Quantity)
WHERE OI.ProductId = ProductId
) AS SL2
I get the error "A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause." so my guess is it's not possible?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No, not possible.

A SELECT returns data, the outer-most query is a select, so it is supposed to be returning data, not changing it. What are you trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CookieMonster SSC Veteran Points: 210 More actions · Answer 2

Thanks for replying GilaMonster,

I'm trying to update suppliers stock for each OrderItem row without using a cursor.

I have a table that im trying to write an update query that will work down the rows in OrderPriority and minus Ordered quantity until it reaches 0 then move to next row.

ordered quantity 7

SupplierId | OrderPriority | Stock

1 | 1 |5

2 | 2 |4

3 | 3 |51

Expected result table

SupplierId | OrderPriority | Stock

1 | 1 |0

2 | 2 |2

3 | 3 |51

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table definitions and easily usable sample data please (as insert statements)

Using SQL 2008 or 2012?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CookieMonster SSC Veteran Points: 210 More actions · Answer 4

Does this help?

CREATE TABLE [dbo].[OrderItems_tbl](

[OrderItemId] [int] IDENTITY(1,1) NOT NULL,

[OrderId] [int] NULL,

[ProductId] [int] NULL,

[Quantity] [int] NULL,

CONSTRAINT [PK_OrderItems_tbl] PRIMARY KEY CLUSTERED

(

[OrderItemId] ASC

)

GO

INSERT INTO OrderItems_tbl (OrderId, ProductId, Quantity) VALUES (1,1,7),(1,2,5)

-------------------------------------------------------------------------

CREATE TABLE [dbo].[StockLevel_tbl](

[StockLevelId] [int] IDENTITY(1,1) NOT NULL,

[ProductId] [int] NULL,

[StockLevel] [int] NULL,

[SupplierId] [int] NULL,

CONSTRAINT [PK_StockLevel_tbl] PRIMARY KEY CLUSTERED

(

[StockLevelId] ASC

)

GO

INSERT INTO StockLevel_tbl (ProductId, StockLevel, SupplierId) VALUES (1,2,1),(1,5,2),(1,38,3),(2,8,1),(2,1,5)

---------------------------------------------------------------------------