0

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!

3
Contributors
2
Replies
21
Views
1 Year
Discussion Span
Last Post by invisal
0

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.

0

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

Edited by invisal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.