0

Having a table that holds some UI settings for a data-table, typically like:

| ID  |   name | alias |   pos  | def_pos | disp |
+-----+--------+-------+--------+---------+------+
|  1  |  name1 | bar   |   1    |    1    |   1  | 
|  2  |  name2 | foo   |   3    |    2    |   1  |
|  3  |  name3 | bar   |   2    |    3    |   1  |
|  4  |  name4 | baz   |   4    |    4    |   0  |
  • ID: PRIMARY
  • name: UNIQUE

Here pos can be updated by user by dragging columns in a graphical interface.

As name and id are unique and I update multiple rows at once I use INSERT and ON DUPLICATE KEY UPDATE instead of UPDATE when changing values. So for example if swapping row 2 and 3 from example above:

INSERT INTO
    ui_data_columns (id, name, pos)
VALUES
    (2, '', 2),
    (3, '', 3)
ON DUPLICATE KEY UPDATE
    pos = VALUES(pos)

As per:

So far so good :P

Now my thought was to add UNIQUE constraint to pos and def_pos as well as one column can not have same position. It is not possible set two column values to the same from the UI, but would be nice to have the constraints as they are unique and ... well, to learn.

Challenge then becomes that if one try to use the KEY UPDATE one run into conflicts when swapping two values. Thus I can not say pos = VALUES(pos) when pos exists - even though it get rectified in same statement (if you get what I mean). Assume they are inserted in sequence so typically:

INSERT INTO ui_data_columns (id, name, pos)
VALUES (2, '', 2)
ON DUPLICATE KEY UPDATE pos = VALUES(pos)

# Error: pos = 2 exists

Even though this fixes is:

INSERT INTO ui_data_columns (id, name, pos)
VALUES (3, '', 3)
ON DUPLICATE KEY UPDATE pos = VALUES(pos)

How to go about doing this?

Small thoughts:

  • Some query magic?
  • Use a temporary copy of the table without constraints clear and fill?
  • Delete records in question first then insert?
  • Drop the constraint for the duration of the insert then re-apply?
  • ...
user3342816
  • 974
  • 10
  • 24
  • `INSERT .. ODKU` inserts/updates records ONE-BY-ONE only, whereas rearranging needs to update two records at the same time (or use some intermediate storage). And I cannot understand why you use INSERT .. ODKU when you need UPDATE... – Akina Mar 27 '20 at 08:14
  • As a trick you may try to use triple update - in `I..ODKU` use `VALUES (2, '', -1), (3, '', 3), (2, '', 2)`... – Akina Mar 27 '20 at 08:17
  • @Akina: I use INSERT as per https://stackoverflow.com/a/34866431 – user3342816 Mar 27 '20 at 08:22

1 Answers1

1

A trick - use intermediate. I.e. perform 3 updates, not 2.

create table test (id int primary key, pos int unique);
insert into test values (1,1),(2,3),(3,2),(4,4);
select * from test;

id | pos
-: | --:
 1 |   1
 3 |   2
 2 |   3
 4 |   4

insert into test values
(3,3), (2,2)
on duplicate key update pos = values(pos);

Duplicate entry '3' for key 'test.pos'

select * from test;

id | pos
-: | --:
 1 |   1
 3 |   2
 2 |   3
 4 |   4

insert into test values
(2,0), (3,3), (2,2)
on duplicate key update pos = values(pos);

select * from test;

id | pos
-: | --:
 1 |   1
 2 |   2
 3 |   3
 4 |   4

db<>fiddle here

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Sweet. Think I can use this approach with some modifications. Perhaps not clear from Q (I'll update) there can be update on multiple rows, not only two, so guess I could build the query by first setting all `pos` values to negative, then to real (when building the query by script.). Only need to change the `pos` to signed (have it set as unsigned as col pos can not be negative IRL) - but likely a better compromise then removing the unique constraint. – user3342816 Mar 27 '20 at 08:38
  • @user3342816 Unsigned may use zero as intermediate value, datatype alter not needed if zero value is not used as regular value. – Akina Mar 27 '20 at 08:40
  • Yes, but if updating multiple records only one can have zero at a time. Would have to build the query to do the inserts in correct order. Have to see how complex that eventually becomes. IIRC MySQL accepts NULL on unique fields, but that is non-standard from what I recall. – user3342816 Mar 27 '20 at 08:46
  • Easy to do: `(1, -3), (2, -2), (4, -1), ... (1, 3), (2, 2), (4, 1), ...` in comparison. – user3342816 Mar 27 '20 at 08:48
  • @user3342816 But you can also use very large values - from MAXVALUE for your datatype and down. – Akina Mar 27 '20 at 08:51
  • (Sorry for chatting here, but) Yes. It is interesting. I have had my mind set at using unsigned where values should be unsigned. A constraint much like UNIQUE. A way to enforce integrity. Reading things like this https://dba.stackexchange.com/q/196738 I'm not so sure anymore https://stackoverflow.com/questions/11515594/when-should-i-use-unsigned-and-signed-int-in-mysql/11515613#comment91610446_11515613 – user3342816 Mar 27 '20 at 09:58