Update table with condition

I have the following problem:

I have table:

TEST_TABLE
x_Id|y_Id
---------
   2|   7
   2|   8
   3|   7
   4|   7
   5|   8

I want remove record if x_Id has y_Id(7) and y_Id(8). And update y_Id to 7 if y_Id = 8 and y_Id(7) is not exist in the unique x_Id.

x_Id and y_Id is composite key.

Example of result:

TEST_TABLE
x_Id|y_Id
-开发者_高级运维--------
   2|   7
   3|   7
   4|   7
   5|   7

Delete duplicates (where for x_Id exists both y_Id's 7 and 8) and update all remaining y_Id where y_Id=8

DELETE FROM TEST_TABLE t1 WHERE y_Id=8 AND EXISTS (SELECT * FROM TEST_TABLE WHERE x_Id=t1.x_Id AND y_Id=7)
UPDATE TEST_TABLE SET y_Id=7 WHERE y_Id=8

These queries do not use corelated subqueries (ones where they must be executed for every row of the outer query), so they should be quite efficient.

delete from test_table
where y_id = 8
and x_id in (select x_id from test_table where y_Id = 7);

update test_table set y_id = 7
where x_id in (select x_id from test_table where y_id = 8)
and x_id not in (select x_id from test_table where y_id = 7);