PostgreSQL, Swap data of certain column in two rows

Question

I have complex function to swap data from same name column in different rows. I read data from first, store it in local temporary variables, read data from second row and if certain conditions are met (min/max), save it to first row and then save temporary variables to second row what is slow and error prone operation. So I thought that maybe same result could be achieved with SQL only. Here is sample data: ``` CREATE TEMP TABLE tbl( id int PRIMARY KEY, doc_num integer, doc_text text ); INSERT INTO tbl VALUES (1, 1, 'First column text1'), (2, 2, 'First column text2'), (4, 3, 'First column text3'), (7, 4, 'First column text4'); ``` Piont is to swap only 'doc_num' column data in desired direction which may be to up or down what I do with separate functions. If I can write a simple query in english that will sound like this: First query: ``` SWAP DOC_NUM in row 2 with DOC_NUM in row 3 IF DOC_NUM in row 3 IS <= MAX(DOC_NUM); ``` Second query: ``` SWAP DOC_NUM in row 3 with DOC_NUM in row…

Accepted Answer

Example: swap doc_num for ids 2 and 4:

```
UPDATE tbl dst
SET doc_num = src.doc_num
FROM tbl src
WHERE dst.id IN(2,4)
AND src.id IN(2,4)
AND dst.id <> src.id -- don't try this at home!
        ;

SELECT * FROm tbl
ORDER BY id;
```

Result:

```
 id | doc_num |      doc_text      
----+---------+--------------------
  1 |       1 | First column text1
  2 |       3 | First column text2
  4 |       2 | First column text3
  7 |       4 | First column text4
(4 rows)
```

PostgreSQL, Swap data of certain column in two rows

Solution

Problem