PostgreSQL, advanced swapping

Question

I get some help on swapping certain data in rows of same table here.
Unfortunately I can't apply those solution in practice because I represent a problem too weak so with offered solutions I can't get expected results.
For that I improve examples and make it very easy to use and for try at the same time more likely my concrete situation with hope that this post will not be treated as duplicate or offensive.

Creating a table:

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text
);

INSERT INTO kalksad1 VALUES
  (12, 2, 5, 'text index 12 doc 2 row 5'),
  (26, 2, 1, 'text index 26 doc 2 row 1'),
  (30, 2, 2, 'text index 30 doc 2 row 2'),
  (32, 4, 1, 'text index 32 doc 4 row 1'),
  (36, 1, 1, 'text index 36 doc 1 row 1'),
  (37, 1, 2, 'text index 37 doc 1 row 2'),
  (38, 5, 1, 'text index 38 doc 5 row 1'),
  (39, 5, 2, 'text index 39 doc 5 row 2'),
  (42, 2, 3, 'text index 42 doc 2 row 3'),
  (43, 2, 4, 'text index 43 doc 2 row 4'),
  (46, 3, 1, 'text index 46 doc 3 row 1'),
  (47, 3, 2, 'text index 47 doc 3 row 2');

What is needed?

To make a query which will swap numbers only in column 'brred' of same 'brkalk'.
Both, 'brred' and 'brkalk' are defined externally, through program.
For example purpose we will take brkalk=2, brred=3.
That mean we should swap brred value only in rows WHEN brkalk=2.

Here are two offered solution's which may be taken as reference.
Both solutions can be useful if they would work.
First one because it can swap rows no matter of order and distance and second because it swap with only first row upper or lower what is most common need. Problem with second solution is that I don't know what it swaps but swaps first and last row instead of row 3 and 2.
That should be repaired.

First query don't work at all in new circumstances so I would like if anyone could repair it. It can be useful for swapping rows no matter of "direction" by external arguments, say swap rows 4 and 1.

Just to clarify, when I say "swap row" I mean to swap ONLY values in 'brred' column which belongs to same 'brkalk' (in this case 2).

First query:

    UPDATE kalksad1 dst 
        SET brred=src.brred 
        FROM kalksad1 src 
        WHERE src.brkalk='2' 
        AND dst.kalk_id IN(2,3) 
        AND src.kalk_id IN(2,3) 
        AND dst.kalk_id <> src.kalk_id;

Second query

    WITH cte1 AS (
        SELECT row_number() OVER(ORDER BY kalk_id ASC) AS row_num, kalk_id, brred 
        FROM kalksad1
        WHERE kalk_id >= 3 ORDER BY kalk_id LIMIT 2
        ) 
    UPDATE kalksad1 AS t 
        SET brred = COALESCE(c2.brred, t.brred) 
        FROM cte1 AS c1 
        LEFT OUTER JOIN cte1 AS c2 ON c2.row_num <> c1.row_num 
        WHERE t.kalk_id = c1.kalk_id AND brkalk='2';

To view data it is best to use:

SELECT * FROM kalksad1 WHERE brkalk='2' ORDER BY brred;

I would like that someone repair upper queries to become workable according to described needs or offer new solution which may be usable for that kind of swapping.

So, thanks to Roman and wildplasser I get this...

Private Function swap_row(ByVal doc_num As Integer, ByVal src_row As Integer, ByVal dest_row As Integer) As Integer

    Dim affected As Integer = 0
    Dim conn As NpgsqlConnection = getConnection()
    Dim t As NpgsqlTransaction = conn.BeginTransaction()

    Using cmd As New NpgsqlCommand( _
        "UPDATE " & myKalkSadTable & " AS dst SET brred = src.brred " & _
        "FROM " & myKalkSadTable & " AS src " & _
        "WHERE(src.brkalk = " & doc_num.ToString & ") " & _
        "AND dst.brkalk = " & doc_num.ToString & " " & _
        "AND dst.brred IN (" & src_row.ToString & "," & dest_row.ToString & ") " & _
        "AND src.brred IN (" & src_row.ToString & "," & dest_row.ToString & ") " & _
        "AND src.kalk_id <> dst.kalk_id", conn)

        affected = CInt(cmd.ExecuteNonQuery())
        cmd.Dispose()
    End Using

    If affected = 2 then t.Commit()
    t.Dispose()
    conn.Close()
    conn.Dispose()

    Return affected
End Function

Private Sub DataGridView2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView2.KeyDown

    If e.Control And e.KeyCode = Keys.Left Then
        swap_row(kalkbr, selected_row, selected_row - 1)
        Refreshlist(kalkbr)
    End If

    If e.Control And e.KeyCode = Keys.Right Then
        swap_row(kalkbr, selected_row, selected_row + 1)
        Refreshlist(kalkbr)
    End If

...etc ...

Please note that if there are N=5 records with brkak=2, there are 5! possible permutations; of these 4! are derangements (IIRC) The number of possible pairings is 10 (there are only two pairs+one single possible), if my math is correct. The intention is unclear: do you want a full derangement or just swap some pairs? — joop, Aug 15 '13 at 11:11
Sorry joop, I don't understand you fully but I just want to swap (as described) and people solves me this good. Now I add NET solution and usage in question. Really elegant. — Wine Too, Aug 15 '13 at 11:26
I got the feeling that you want more than swapping **two** rows (swap implies exactly two items). If you want to update the _tabbing order_ for a whole "block" of records with equal brkalk=2, please see this answer (also by wildplasser) : http://stackoverflow.com/a/14092775/2235885 — joop, Aug 15 '13 at 13:23
I get what I want here but link you gave is highly interesting. I will need those as reference very soon. Thanks. — Wine Too, Aug 15 '13 at 15:23

score 1 · Accepted Answer · answered Aug 15 '13 at 10:44

1

for first one you have to filter brkalk on both dst and src:

update kalksad1 as dst set
    brred = src.brred 
from kalksad1 as src 
where
    src.brkalk = 2 and dst.brkalk = 2 and
    dst.brred in (2,3) and
    src.brred in (2,3) and
    src.kalk_id <> dst.kalk_id;

sql fiddle demo

I think second one is too complicated, I've created it when I thought that you want to swap row with exact ID and next one

answered Aug 15 '13 at 10:44

Roman Pekar

107,110
28
195
197

Probably because I explain too bad. But this one REALLY ROCKS!! Thank you for repairing it and give me final solution after few days of hard trying to get it. – Wine Too Aug 15 '13 at 10:51

PostgreSQL, advanced swapping

1 Answers1

Linked