How to flip values of two rows?-CodePudding

ID  TYPE      ORDER_INDEX  CITY_ID
-----------------------------------
1   CAT            1          1
2   DOG            2          1
3   CAT            4          2 
4   DOG            5          2
5   BEE            9          1

For each city I need to swap the order_index of cat and dog.

So city with ID 1 should have cat=2 and dog=1, city with ID 2 would have cat=5 and dog=4.

How can this be done with pure SQL?

CodePudding user response：

Use an update:

UPDATE yourTable t1
SET ORDER_INDEX = (SELECT t2.ORDER_INDEX FROM yourTable t2
                   WHERE t2.CITY_ID = t1.CITY_ID AND
                         t2.TYPE IN ('CAT', 'DOG') AND
                         t2.TYPE <> T1.TYPE)
WHERE TYPE IN ('CAT', 'DOG');

The above update logic assumes that you wish to do the ORDER_INDEX swap between dog/cat pair records having the same CITY_ID.

CodePudding user response：

Well, if you don't care about ID, then there's a simple option which does what you wanted:

Before:

SQL> select * from test order by id;

        ID TYP ORDER_INDEX    CITY_ID
---------- --- ----------- ----------
         1 CAT           1          1
         2 DOG           2          1
         3 CAT           4          2
         4 DOG           5          2
         5 BEE           9          1

Update:

SQL> update test set
  2    type = decode(type, 'DOG', 'CAT', 'DOG')
  3  where type in ('CAT', 'DOG');

4 rows updated.

After; requirement was

city with ID 1 should have cat=2 and dog=1, city with ID 2 would have cat=5 and dog=4.

SQL> select * from test order by id;

        ID TYP ORDER_INDEX    CITY_ID
---------- --- ----------- ----------
         1 DOG           1          1  --> city with ID = 1 has DOG = 1 ...
         2 CAT           2          1  --> ... and CAT = 2
         3 DOG           4          2  --> city with ID = 2 has DOG = 4 ...
         4 CAT           5          2  --> ... and CAT = 5
         5 BEE           9          1

SQL>