delete from table based on where exists

  • 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

  • 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

  • -- 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

    )

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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!!

  • 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).

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Abu Dina (1/15/2014)


    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?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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:

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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....:-)

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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