Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: why doesn't this work

    When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing

    select
    eqnow.empnumber,
    eqnow_names.empnumber,
    eqnow_names.names
    --delete
    from
    eqnow
    inner join eqnow_names
    on eqnow.empnumber = eqnow_names.empnumber
    where
    eqnow_names.names is null



    i get this error
    Server: Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'inner'.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem lies here: you have a join in the delete but you haven't said which table you want rows deleted from

    assuming you want to delete rows from eqnow, you would write
    Code:
    delete eqnow
      from eqnow
    inner 
      join eqnow_names
        on eqnow_names.empnumber = eqnow.empnumber
     where eqnow_names.names is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    thanks... I ended up using an exist clause.... subquery to delete...
    i tried what you had above i stilled ended up with a syntax...
    error... hmmmm

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I've seen similar issues when using this type of thing to perform deletes with joins. I've had to do (essentially) this:
    Code:
    delete e
      from eqnow e
    inner 
      join eqnow_names en
        on en.empnumber = e.empnumber
     where en.names is null
    Don't ax me why it works this way and not using the tablenames alone.

    I'd sure like to know because I like the way it looks better when I don't have to reference the delete target table with the nondescript table_alias "e".
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •