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".
