hello all. i have the following code...

SELECT *
    FROM data
    WHERE no_2 = no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 > no_4 + 1
  UNION
    SELECT *
    FROM data
    WHERE no_2 > no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 = no_4 + 1

...which works fine. i need to remove all the records produced by this query from a second table, "data2", which has duplicate data. i've tried...

DELETE
    FROM data2
    WHERE no_2 = no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 > no_4 + 1
  UNION
    delete
    FROM data2
    WHERE no_2 > no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 = no_4 + 1

...with no success. can anyone point me in the right direction? thanks in advance!

Try this (untested by me)

DELETE FROM data2 where id in (
    SELECT id
    FROM data
    WHERE no_2 = no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 > no_4 + 1
  UNION
    SELECT id
    FROM data
    WHERE no_2 > no_1 + 1
      AND no_3 = no_2 + 1
      AND no_4 = no_3 + 1
      AND no_5 = no_4 + 1
)

I guessed at 'id' being a column, use whatever is appropriate to return one column for the 'in' clause.

EDIT: I misread the post and provide wrong answer. I don't how to delete the post.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.