REPLACE INTO with SELECT condition

Question

I have table:

mysql> desc dialog;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          | NO   | PRI | NULL    | auto_increment |
| uid1      | int(11)          | NO   | MUL | NULL    |                |
| uid2      | int(11)          | NO   | MUL | NULL    |                |
| mid       | int(11)          | NO   |     | NULL    |                |
| anonym_id | int(10) unsigned | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

and the same one:

mysql> desc dialogs;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          | NO   | PRI | NULL    | auto_increment |
| uid1      | int(11)          | NO   | MUL | NULL    |                |
| uid2      | int(11)          | NO   | MUL | NULL    |                |
| mid       | int(11)          | NO   |     | NULL    |                |
| anonym_id | int(10) unsigned | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

The 1-st one - dialog has a duplicate values which I don't need in the 2-nd - dialogs, the structure is the same, except unique index in dialogs table:

mysql> show index from dialogs;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dialogs |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          0 | uid1_uid2 |            1 | uid1        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          0 | uid1_uid2 |            2 | uid2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          1 | uid2      |            1 | uid2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          1 | anonym_id |            1 | anonym_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

I've found the way to insert into dialogs from dialog without duplicate key, but one:

REPLACE INTO dialogs SELECT * FROM dialog;

Works like a charm, but - I need either to test IF uid2>uid1 THEN switch them vice-versa and insert uid1=uid2 and uid2=uid1. Is there a solution to do almost the same, but with this clause with 1 query? I`d tried something like, but this query failed to execute:

REPLACE INTO dialogs 
    SELECT IF uid1<uid2 THEN * 
           ELSE id, uid2, uid1, mid, anonym_id 
END FROM dialog;

If so - please, post an example - I`ll try it.

Thx.

Is it ok with you that `REPLACE` is actually doing a deletion and then adding back, thereby incrementing the auto-increment? http://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke (Is that your _desired_ behavior?) — Michael Berkowski, May 17 '14 at 12:46
Please also post a small sample rowset for each table showing the input and desired output. If possible, please set it up at http://sqlfiddle.com. However, I think I understand what you're after so I'll post below. — Michael Berkowski, May 17 '14 at 12:51

score 0 · Accepted Answer · edited May 23 '17 at 12:11

Instead of the REPLACE INTO which will increment your ids, you can use INSERT IGNORE to leave them in place if already present. As for switching the values of uid1, uid2, set the values in a CASE.

INSERT IGNORE INTO dialogs (id, uid1, uid2, mid, anonym_id)
  SELECT
    id,
    /* First prefer uid1, then the opposite for uid2 */
    CASE WHEN uid1 < uid2 THEN uid1 ELSE uid2 END,
    CASE WHEN uid1 < uid2 THEN uid2 ELSE uid1 END,
    mid,
    anonym_id
  FROM dialog

A little more on the difference between REPLACE and INSERT IGNORE...

REPLACE INTO with SELECT condition

1 Answers1