Post reply

help with Delete statement

  • September 5, 2007 at 7:40 am

    #178325

    Hi, 

    I am trying to delete the following data but I am getting the following errors:

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'as'.

    Msg 156, Level 15, State 1, Line 8

    Incorrect syntax near the keyword 'and'

    Can anyone see what I'm doing wrong?  Thanks in advance.

     

    delete from asqcrr.occ.dbo.MCommPLOrders as source

    where

    not exists

    (

    select

    id

    from

    [csr].occ.dbo.tbl_PLOrdersDrop as dest

    where

    dest.id = source.id

    )

    and

    convert(varchar, timeofcode, 112)

    = convert(varchar, getdate(), 112)

  • September 5, 2007 at 7:48 am

    #731504

    Which table are you deleting from?

    The delete statement has two from clauses. The first one specifies the table you are deleting from, the second is for the conditions. You can only alias in the second FROM clause.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 5, 2007 at 7:52 am

    #731506

    Wow, Thanks Andras, your reply was very quick and accurate.  Thank you for the expanation, it worked.

     

  • September 5, 2007 at 10:40 am

    #731580

    If you're aliasing a Delete table for a join, the same principal works.  Example:

    Delete from MyTable

    from MyTable t1

    join OtherTable t2

    on t1.A = t2.A  ...

    Basically, you have to list the FROM clause twice in order to alias and you can't (that I know of) use "Delete from t1" as your first statement.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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