Delete in a self join

Question

Post reply

Delete in a self join

joe joe-152023

SSCommitted

Points: 1819
More actions
October 6, 2005 at 3:41 pm

#91748

How do I delete the whole record (row)????
I tried with this,
Delete *
from Mytab as a
where
a.Airport = 'A' and
exists (select b.Road, b.MainRoad from Mytab as b
          where b.Airport = 'A'
           and b.Road = a.Road
           and b.MainRoad = a.MainRoad
          group by b.Road, b.MainRoad
          having count(*) > 1)
It comes up with,
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.
Help is needed within short time
Joejoe

Viewing 9 posts - 1 through 8 (of 8 total)

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

kgayda SSCrazy Points: 2157 More actions · Answer 1

leave out the *

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 2

Thanks kgayda,

But it gives me,

Server: Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'as'.

And there is no way to do the query without a self join.

Regards Joejoe

kgayda SSCrazy Points: 2157 More actions · Answer 3

Are you trying to delete all of the rows that are duplicates or all but one?

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 4

only the rows that are "duplicates" in three columns

kgayda SSCrazy Points: 2157 More actions · Answer 5

sounds like your real problem is the you need a unique index on the three columns to prevent future duplications. There are several ways to do the deletion but what I would do if I were you as a quick and dirty solution is to do a quick script that dumps the dup into a temp table, delete the matches, and then add the index

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 6

It's a very very long story. Sometime reality overseeds fantasy. The database I'm working with is violating everything, even the most basic database design rules.

And the worst thing is that there is are reasons for this.

Once again thanks

philcart SSC-Forever Points: 47794 More actions · Answer 7

Wouldn't this be a case of using the non-ANSI Double FROM and using a derived table.

Delete from Mytab
from Mytab as a
    inner join (
      select Road, MainRoad 
      from Mytab
      where b.Airport = 'A'
      group by b.Road, b.MainRoad
      having count(*) > 1
    ) as b
   on b.Road = a.Road
   and  b.MainRoad = a.MainRoad

--------------------
Colt 45 - the original point and click interface

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 8

Hey Phill Carter,

Thanks, it just missed a littel "as b"

Delete from Mytab

from Mytab as a

inner join (

select Road, MainRoad

from Mytab as b

where b.Airport = 'A'

group by b.Road, b.MainRoad

having count(*) > 1

) as b

on b.Road = a.Road

and b.MainRoad = a.MainRoad

You are all heroes!!!

Regrads joejoe