changing the info in a table with multiple index columns

[Tony W.]

I have a MySQL question about changing the info in a table:

I am using MySQL Server version: 3.23.58

I have a table called sign_region2 and it contains thousands of
references to signs and the regions they are in.

mysql> desc sign_region2;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id_sign | int(11) | | PRI | 0 | |
| id_region | int(11) | | PRI | 0 | |
+-----------+---------+------+-----+---------+-------+

This table keeps track of which signs are in which regions. Note that
the Primary Key is a multiple-column index (id_sign and id_region).

mysql> select * from sign_region2 limit 20;
+---------+-----------+
| id_sign | id_region |
+---------+-----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 6 | 5 |
| 7 | 5 |
| 8 | 2 |
| 10 | 2 |
| 11 | 1 |
| 11 | 2 |
| 11 | 3 |
| 11 | 4 |
+---------+-----------+
20 rows in set (0.00 sec)

I need to take all "id_region = 4" and make them "id_region = 2"
except where "id_region = 2" already exists. In that case I'd just
need to delete where "id_region = 4".

Remember that there are thousands of references in this table. Some
signs are in region 2, some are in region 4, some both, some neither.
What query would I use to accomplish this?

The problem is that the DB won't allow identical instances of the
id_sign and id_region combinations (because they must be unique
because they are primary keys).

An example:
Let's say I've got a table that looks like this...
+---------+-----------+
| id_sign | id_region |
+---------+-----------+
| 12 | 1 |
| 12 | 3 |
| 12 | 4 |
| 12 | 5 |
+---------+-----------+

But I need it to look like this...
+---------+-----------+
| id_sign | id_region |
+---------+-----------+
| 12 | 1 |
| 12 | 2 |
| 12 | 3 |
| 12 | 5 |
+---------+-----------+

But for thousands of records not just the ones with "id_sign = 12".

[Bill Karwin]

Tony W. wrote:

I need to take all "id_region = 4" and make them "id_region = 2"
except where "id_region = 2" already exists. In that case I'd just
need to delete where "id_region = 4".

The problem is that the DB won't allow identical instances of the
id_sign and id_region combinations (because they must be unique
because they are primary keys).

Probably the simplest solution is to do it in two steps:

insert ignore into sign_region2 (id_sign, id_region)
select id_sign, 2 from sign_region2 where id_region = 4;
delete from sign_region2 where id_region = 4;

The "ignore" keyword is a MySQL extension to SQL. It allows the insert
operation to treat key violations as warnings instead of fatal errors.
So the row inserts that aren't collisions succeed, even though those
that would result in collision fail. See the docs on INSERT for more
details.

Regards,
Bill K.