none
DELETE statement not right RRS feed

  • Question

  • DELETE * from [Orders].[dbo].[Spooling] S join [Orders].[dbo].[MessageRefCount] mrc
    on S.[uidMessageID ]=mrc.[uidMessageID ] where mrc.snRefCount='-1'

    Can someone please advise how to fix this DELETE statement ?

    I am getting * is not recognized


    RH

    Thursday, September 19, 2019 5:16 PM

All replies

  • Try without “*”:

    DELETE from …

    • Proposed as answer by Rafael S. Melo Thursday, September 19, 2019 5:39 PM
    Thursday, September 19, 2019 5:28 PM
  • DELETE from [Orders].[dbo].[Spooling]   
    WHERE [uidMessageID ] IN (SELECT S.[uidMessageID ] from [Orders].[dbo].[Spooling] S join [Orders].[dbo].[MessageRefCount] mrc
    on S.[uidMessageID ]=mrc.[uidMessageID ] where mrc.snRefCount='-1')

    Thursday, September 19, 2019 5:58 PM
    Moderator
  • Hi Dan2890,

    Using '*' is not correct while using DELETE.

    For your question,

    1.If you want to delete the entry in table named 'Spooling', then use like below:

    DELETE S from [Orders].[dbo].[Spooling] S join [Orders].[dbo].[MessageRefCount] mrc
    on S.[uidMessageID ]=mrc.[uidMessageID ] where mrc.snRefCount='-1'
    2.If you want to delete the entry in table named 'MessageRefCount', then use like below:
    DELETE mrc from [Orders].[dbo].[Spooling] S join [Orders].[dbo].[MessageRefCount] mrc
    on S.[uidMessageID ]=mrc.[uidMessageID ] where mrc.snRefCount='-1'

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Thursday, September 19, 2019 8:10 PM
  • Hi Dan2890,

     

    I will provide you a detailed example . Please refer to it.

     
    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go
    create table test (A int, B int )
    insert into test values (1,2),(3,4)
    go
    delete from test where A=1
    select * from test 
    /*
    A           B
    ----------- -----------
    3           4
    */
    go
    delete a from test a where A=3
    select * from test 
    /*
    A           B
    ----------- -----------
    */

    For more information, please refer to DELETE (Transact-SQL).

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 20, 2019 3:03 AM
  • Hi 

    Could you try the below code once and hope it fixes the issue.

    DELETE S
    FROM [Orders].[dbo].[Spooling] S 
    INNER JOIN [Orders].[dbo].[MessageRefCount] mrc
    ON S.[uidMessageID ]=mrc.[uidMessageID ] 
    WHERE mrc.snRefCount='-1'

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Friday, September 20, 2019 3:20 AM
  • IF OBJECT_ID('Spooling_test') IS NOT NULL 
    drop table  Spooling_test 
    IF OBJECT_ID('MessageRefCount_test') IS NOT NULL 
    drop table  MessageRefCount_test 
    
    go
    create table Spooling_test (uidMessageID int, snRefCount int )
    insert into Spooling_test values (1,2),(2,4),(3,3)
    
    create table MessageRefCount_test (uidMessageID int, snRefCount int )
    insert into MessageRefCount_test values (1,2),(2,4),(3,-1)
    
    --Following queries are all working
    
    DELETE S
    FROM Spooling_test S 
    Where Exists(Select 1 
    from MessageRefCount_test mrc 
    where S.uidMessageID=mrc.uidMessageID 
    and mrc.snRefCount=-1)
    
    --DELETE from  [Spooling_test]   
    --WHERE [uidMessageID] IN (SELECT S.[uidMessageID] 
    --from  [Spooling_test] S join  [MessageRefCount_test] mrc
    --on S.[uidMessageID]=mrc.[uidMessageID] where mrc.snRefCount='-1')
    
    
    --DELETE S
    --FROM [Spooling_test] S 
    --INNER JOIN  [MessageRefCount_test] mrc
    --ON S.[uidMessageID]=mrc.[uidMessageID] 
    --WHERE mrc.snRefCount=-1
    
    select * from Spooling_test
    
    drop table MessageRefCount_test, Spooling_test

    Friday, September 20, 2019 2:27 PM
    Moderator
  • Hi

    Just checking in to see if my initial answer helped or you still facing any issues.

     

    If my initial reply answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 22, 2019 3:29 AM