January 15, 2014 at 5:08 am
hi
i'm trying to delete products in a media event details table where no base forecast exists for the customer/product.
i have the list of products for each account which need to be deleted by using:
select a.AccountId, b.StockId
from dbo.MediaHeader a
Join dbo.MediaDetails b
on a.MediaId = b.MediaId
where not exists(
select AccountId, StockId
from dbo.BaseForecast c
where Quantity > 0 and a.AccountId = c.AccountId and b.StockId = c.StockId
group by AccountId, StockId
)
group by a.AccountId, b.StockId
... this returns the products i need to remove for each account. but when i try to add the delete statement i get an error. so this...
delete from dbo.MediaDetails
where exists(
select a.AccountId, b.StockId
from dbo.MediaHeader a
Join dbo.MediaDetails b
on a.MediaId = b.MediaId
where not exists(
select AccountId, StockId
from dbo.BaseForecast c
where Quantity > 0 and a.AccountId = c.AccountId and b.StockId = c.StockId
group by AccountId, StockId
)
group by a.AccountId, b.StockId)
...gives me this error..
Msg 512, Level 16, State 1, Procedure AggregateMediaDelete, Line 36
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
whats happening???
thanks
January 15, 2014 at 5:28 am
Your first query is returning two columns: a.AccountId, b.StockId
Then your delete is using the EXISTS operator which is fair enough but the inner query returns multiple rows so how do you expect SQL to know which ones to delete?
As far as I know the EXISTS operator is used like this:
SELECT something
FROM Table1 AS a
WHERE EXISTS (SELECT 1 FROM TableB AS b WHERE a.someID = b.someID)
Notice the join in the inner query. Yours doesn't have a join so SQL Server is getting multiple rows returned in the inner query and it doesn't know which ones to delete! Hope this makes sense.
If you can provide some sample data then that'd be great.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
January 15, 2014 at 6:40 am
-- if this outputs the results you want to delete,
SELECT b.*
FROM dbo.MediaDetails b
INNER Join dbo.MediaHeader a
ON a.MediaId = b.MediaId
WHERE NOT EXISTS (
SELECT 1
FROM dbo.BaseForecast c
WHERE c.Quantity > 0
AND a.AccountId = c.AccountId
AND b.StockId = c.StockId
--group by AccountId, StockId
)
-- then change it to this for the delete:
DELETE b
FROM dbo.MediaDetails b
INNER Join dbo.MediaHeader a
ON a.MediaId = b.MediaId
WHERE NOT EXISTS (
SELECT 1
FROM dbo.BaseForecast c
WHERE c.Quantity > 0
AND a.AccountId = c.AccountId
AND b.StockId = c.StockId
--group by AccountId, StockId
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 15, 2014 at 11:14 am
thanks for the replies, still not working.
annoyingly this test code seems to work....
-- create the test tables
create table test1(
MediaDetailId int identity (1,1) not null,
Qty int null
)
create table test2(
MediaDetailId int
)
-- insert some rows
insert into test1 (Qty)
select 500 union
select 200 union
select 300 union
select 100 union
select 150 union
select 750 union
select 500 union
select 900 union
select 850
insert into test2
select 1 union
select 3 union
select 7 union
select 8
select * from test1
select * from test2
-- what i need is to delete all media id's from test1 where they exist in test2, so....
delete a
from test1 a
where exists (select MediaDetailId from test2 b where a.MediaDetailId = b.MediaDetailId)
select * from test1
-- tidy up
drop table test1
drop table test2
however, when i try to do something similar with my table it doesn;t. I've now inserted all the MediaDetailId's i want to delete into a temp table called #t1. so based on the above (which works) i write...
delete a
from MediaDetails a
where exists (select MediaDetailId from #t1 b where a.MediaDetailId = b.MediaDetailId)
..and i'm still getting the error:
Msg 512, Level 16, State 1, Procedure AggregateMediaDelete, Line 36
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
confused!!
January 16, 2014 at 1:44 am
The message you're getting suggests you have a trigger on the table you are deleting from, and that the trigger has been written to handle single-row changes, not the set-changes you are attempting to perform. A surefire way to tell is if there are variables in the trigger code. If it is a row-scoped trigger then you have two choices - rewrite the trigger (recommended) or perform your deletes one at a time (not recommended).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2014 at 2:09 am
Abu Dina (1/15/2014)
Your first query is returning two columns: a.AccountId, b.StockIdThen your delete is using the EXISTS operator which is fair enough but the inner query returns multiple rows so how do you expect SQL to know which ones to delete?
There's nothing wrong with an EXISTS subquery returning multiple rows. All the EXISTS checks for is whether there are rows or not. If there are, the EXISTS returns true, if not, it returns FALSE.
This is legal, though probably stupid
DELETE FROM Table1 WHERE EXISTS (SELECT col1, col23, col75, othercolumn FROM TableWithAMillionrows)
Yours doesn't have a join so SQL Server is getting multiple rows returned in the inner query and it doesn't know which ones to delete!
No. It knows exactly which ones to delete. Any where the evaluation of the EXISTS returns 1 or more rows.
There's nowhere in the delete posted that there is a subquery that must return one row. I would also suspect a badly written trigger that's throwing the error, not lease because the error references an object name "AggregateMediaDelete". Look at triggers on the table, look for one with that name and see what the code in that trigger is doing. The cause of the error is in there.
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
January 16, 2014 at 2:52 am
No. It knows exactly which ones to delete.
Does it?
CREATE TABLE SSC1 (ID INT, Name VARCHAR(50))
INSERT INTO SSC1 SELECT 1, 'Abu Dina' UNION ALL SELECT 2, 'Howard Zinn' UNION ALL SELECT 3, 'Bobby Fischer'
CREATE TABLE SSC2 (ID INT, Name VARCHAR(50))
INSERT INTO SSC2 SELECT 5, 'Abu Dina' UNION ALL SELECT 7, 'Howard Zinn'
SELECT * FROM SSC1
SELECT * FROM SSC2
SELECT * FROM SSC2 WHERE EXISTS (SELECT ID, Name from SSC1)
SELECT * FROM SSC2 AS a WHERE EXISTS (SELECT ID, Name FROM SSC1 AS b WHERE a.ID = b.ID)
DROP TABLE SSC1
DROP TABLE SSC2
I know how EXISTS works but I can see how my original response is misleading. It's just better to use a syntax similar to
SELECT * FROM SSC2 AS a WHERE EXISTS (SELECT ID, Name FROM SSC1 AS b WHERE a.ID = b.ID)
All examples in http://technet.microsoft.com/en-us/library/ms188336.aspx do the same and that's how I've used the EXISTS operator for the last 10 years.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
January 16, 2014 at 3:03 am
Abu Dina (1/16/2014)
No. It knows exactly which ones to delete.
Does it?
Yes. Any row for which the EXISTS predicate evaluates to TRUE.
Now, if you don't use a join, that'll be every single row of the table, which probably isn't what you intended, but it's exactly what you specified the query to do and exactly what SQL did.
'Did not do as I intended' != 'SQL doesn't know what row to delete'
As for 'better', depends what you're intending.
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
January 16, 2014 at 3:27 am
exactly correct ChrisM@Work. i had a trigger which was a single row update on the table i was trying to delete from. I disabled the trigger and my t-sql ran fine. now to try and get them to work together.....
:unsure:
January 16, 2014 at 3:29 am
If you can post the trigger code, we can probably get it to behave properly.
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
January 16, 2014 at 3:32 am
Abu Dina (1/16/2014)
... It's just better to use a syntax similar to
SELECT * FROM SSC2 AS a WHERE EXISTS (SELECT ID, Name FROM SSC1 AS b WHERE a.ID = b.ID)All examples in http://technet.microsoft.com/en-us/library/ms188336.aspx do the same and that's how I've used the EXISTS operator for the last 10 years.
I disagree with this. Us simple coders need all the help we can get. Including a column name in the EXISTS subquery implies significance: using two reinforces that implication. It might be best if we could use something like SELECT 'ATLEASTONEROW';
SELECT * FROM SSC2 AS a
WHERE EXISTS (SELECT 'ATLEASTONEROW' FROM SSC1 AS b WHERE a.ID = b.ID)
Personally I use SELECT 1, but I might change it to the above.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 16, 2014 at 3:41 am
ChrisM@Work (1/16/2014)
Personally I use SELECT 1, but I might change it to the above.
So do I actually. I've always used SELECT 1 because ultimately what the sub query returns is irrelevant. It's what the EXISTS evaluates to that matter but for a simple coder SELECT 1 is more confusing surely?! 😛
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
January 16, 2014 at 3:52 am
i'm relatively new to t-sql and i've written it as it works so please don't ban me from the forum based on this. It's also quite lengthy.....
first the trigger. It's an update trigger which aggregates the data in the table to a forecast table. (there's also an insert and delete trigger which do similar things. i'm going to merge them and capture the action type) ....
ALTER TRIGGER [dbo].[AggregateMediaUpdate]
ON [dbo].[MediaDetails]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- insert/delete variables
declare @StartDate datetime
declare @EndDate datetime
declare @stockId int
declare @percentNew as decimal(6,2)
declare @percentOld as decimal(6,2)
declare @accountId as int
-- set start date buy in date if enetered else start date
set @StartDate = (select case
when isnull(mh.StartDateBuyIn,0) = 0 then mh.StartDate
else mh.StartDateBuyIn
end as StartDate
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
-- set end date buy in date if enetered else end date
set @EndDate = (select case
when isnull(mh.EndDateBuyIn,0) = 0 then mh.EndDate
else mh.EndDateBuyIn
end as EndDate
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
-- stock code
set @stockId = (select i.StockId from inserted i)
-- get the new % uplift
set @percentNew = (select i.UpliftPct from inserted i)
-- get the old % uplift
set @percentOld = (select d.UpliftPct from deleted d) * -1
-- get the account id
set @accountId = (select AccountId
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
-- REMOVE ORIGINAL UPLIFT VALUE
begin
exec MediaInsertNewAggregate
@StartDate = @StartDate,
@EndDate = @EndDate,
@stockId = @stockId,
@percent = @percentOld,
@accountId = @accountId;
end
-- ADD NEW UPLIFT VALUE
begin
exec MediaInsertNewAggregate
@StartDate = @StartDate,
@EndDate = @EndDate,
@stockId = @stockId,
@percent = @percentNew,
@accountId = @accountId;
end
-- DELETE 0 VALUE LINES
begin
delete from dbo.MediaDetails where UpliftPct = 0
end
END
... this call a dynamic sql stored proc called MediaInsertNewAggregate which actually does the aggregation. It is a % uplift value based on there being a base forecast for the account/product....
ALTER proc [dbo].[MediaInsertNewAggregate] (
@StartDatedatetime = null,
@EndDatedatetime = null,
@StockIdint = null,
@Percentdecimal(6,2) = 0,
@AccountIdint = null)
as
begin
-- set default values for stockcode passed NULL or make sure that any other character string passed is truly NULL if intended to be.
SELECT@StockId = NULLIF(@StockId, '')
SELECT@AccountId = NULLIF(@AccountId, '')
-- define the dynamic sql parameters
DECLARE @SQLParms NVARCHAR(MAX)
SET@SQLParms = N'@StartDate datetime = null, @EndDate datetime = null, @StockId varchar(8000) = null, @Percent as decimal(6,2) = 0, @AccountIdint = null'
-- build the dynamic sql string
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = N'
select
a.TimeKey, a.AccountId, a.StockId, (d.Days * round(a.Quantity/nullif(converT(decimal(18,2),c.MonthDays),0),2)) * @percent MediaValueActual
-- base forecast
from dbo.BaseForecast a
-- month days
left join dbo.CalendarMonth c
on a.TimeKey = c.TimeKey
-- month days selected
left join(
select * from MonthDaysBetweenDates (@StartDate-1, @EndDate)) d
on c.Year = d.Year
and c.MonthNumber = d.MonthNumber
where Quantity > 0
and d.Days is not null ' +
+ CASE WHEN @stockId IS NOT NULL
THEN 'and a.StockId = @StockId ' + CHAR(10)
ELSE ''
END + CHAR(10) +
+ CASE WHEN @AccountId IS NOT NULL
THEN 'and a.AccountId = @AccountId ' + CHAR(10)
ELSE ''
END + CHAR(10) +
'order by a.TimeKey, a.AccountId, a.StockId'
-- do the upsert
begin
-- create a table variable to hold the result
declare @stageTbl table(
TimeKey int,
AccountId int,
StockId int,
Quantity decimal (18,2))
-- return result of dynamic sql string into table variable
insert into @stageTbl EXEC sp_executesql @sql, @SQLParms,
@StartDate = @StartDate,
@EndDate = @EndDate,
@StockId = @StockId,
@percent = @percent,
@AccountId = @AccountId;
-- variable to hold records for insert
declare @updated_ids table (TimeKey int, AccountId int, StockId int)
-- update
update p
set p.Quantity = p.Quantity + s.Quantity
output inserted.TimeKey, inserted.AccountId, inserted.StockId
into @updated_ids
from Media p, @stageTbl s
where p.TimeKey = s.TimeKey and p.AccountId = s.AccountId and p.StockId=s.StockId
-- insert
insert into Media(TimeKey, AccountId, StockId, Quantity)
select TimeKey, AccountId, StockId, Quantity
from @stageTbl s
where not exists(
select 1 from @updated_ids i
where i.TimeKey = s.TimeKey and i.AccountId = s.AccountId and i.StockId = s.StockId);
end
-- kill the temp tables
if object_id('tempdb..#stockIds') is not null
drop table #stockIds
end
go easy on me....:-)
January 16, 2014 at 4:25 am
This is what's causing the errors
where MediaId = (select MediaId from inserted))
When you delete multiple rows, there are multiple rows in inserted (it's not a for each row trigger) and so that query fails.
What's the trigger supposed to do? It's going to need a complete rewrite in a set-based method (and without the procedure call) in order to work properly.
Edit: Can you post the AFTER DELETE trigger please, not the update one? Though I suspect they all have the same problem
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
January 16, 2014 at 5:01 am
a media event is created which has accountId, stockId, startDate, endDate. (it also has some other details about the event).
after insert, delete, update i need
1) to calculate a % uplift on a base forecast value.
2 ) upsert the values to a aggregate table (monthly values).
hope that makes sense.
EDIT: Delete Trigger
ALTER TRIGGER [dbo].[AggregateMediaDelete]
ON [dbo].[MediaDetails]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @StartDate datetime
declare @EndDate datetime
declare @stockId int
declare @percentNew as decimal(6,2)
declare @percentOld as decimal(6,2)
declare @accountId as int
set @StartDate = (select case
when isnull(mh.StartDateBuyIn,0) = 0 then mh.StartDate
else mh.StartDateBuyIn
end as StartDate
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
set @EndDate = (select case
when isnull(mh.EndDateBuyIn,0) = 0 then mh.EndDate
else mh.EndDateBuyIn
end as EndDate
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
set @stockId = (select i.StockId from inserted i)
set @percentNew = (select i.UpliftPct from inserted i)
set @percentOld = (select d.UpliftPct from deleted d) * -1
set @accountId = (select AccountId
from dbo.MediaHeader mh
where MediaId = (select MediaId from inserted))
-- delete media aggregates values
begin
exec MediaInsertNewAggregate
@StartDate = @StartDate,
@EndDate = @EndDate,
@stockId = @stockId,
@percent = @percentOld,
@accountId = @accountId;
end
END
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply