How can I replace / update all instances of each row in a column with matching values from another table?
Storing data in comma-separated strings didn't fit me, and it's not something I can change in my database, so bear with me. I have searched quite a lot on the internet and on stackoverflow, but I could not find a solution to this, even if it is possible to use MySQL.
I am trying to replace all instances of each unique row from table1 with the corresponding value from table2. I've tried wildcards, replace, update, join, etc. and I'm just not sure how to get it to work. I know one solution will replace () for every row, but table2 has more than 200 rows, so that means there will be more than 200 nesting times.
Here is what I am trying to do. I have two tables, table1:
+------+-------------+
| Item | Code |
+------+-------------+
| 1 | 614 |
+------+-------------+
| 2 | 212,614,415 |
+------+-------------+
| 3 | 212,303 |
+------+-------------+
| ... | ... |
+------+-------------+
and table2:
+------+-------------------+
| Code | Name |
+------+-------------------+
| 614 | Columbus, OH |
+------+-------------------+
| 212 | New York, NY |
+------+-------------------+
| 415 | San Francisco, CA |
+------+-------------------+
| 303 | Ft. Worth, TX |
+------+-------------------+
| ... | ... |
+------+-------------------+
I want to replace the codes from table 1 with the corresponding values from table2 to get this result:
+------+---------------------------------------------+
| Item | Code |
+------+---------------------------------------------+
| 1 | Columbus, OH |
+------+---------------------------------------------+
| 2 | New York, NY,Columbus, OH,San Francisco, CA |
+------+---------------------------------------------+
| 3 | New York, NY,Ft. Worth, TX |
+------+---------------------------------------------+
| ... | ... |
+------+---------------------------------------------+
source to share
This should do it (see last query below). I've included commas in join, so both the id of something like 12 doesn't match where you have it and the id of 212 (for example).
drop table if exists table1;
drop table if exists table2;
create table table1(
item int,
code varchar(64)
);
create table table2(
code int,
name varchar(64)
);
insert into table1 values (1, '614');
insert into table1 values (2, '212,614,415');
insert into table1 values (3, '212,303');
insert into table2 values(212, 'New York, NY');
insert into table2 values(303, 'Ft. Worth, TX');
insert into table2 values(415, 'San Francisco, CA');
insert into table2 values(614, 'Columbus, OH');
select * from table1
+ --------- + --------- +
| item | code |
+ --------- + --------- +
| 1 | 614 |
| 2 | 212,614,415 |
| 3 | 212,303 |
+ --------- + --------- +
3 rows
select * from table2
+ --------- + --------- +
| code | name |
+ --------- + --------- +
| 212 | New York, NY |
| 303 | Ft. Worth, TX |
| 415 | San Francisco, CA |
| 614 | Columbus, OH |
+ --------- + --------- +
4 rows
select
t1.item,
t2.name
from
table1 t1 join table2 t2 on (
t1.code = t2.code
or t1.code like concat(t2.code, ',%')
or t1.code like concat('%,', t2.code, ',%')
or t1.code like concat('%,', t2.code)
)
order by t1.item
+ --------- + --------- +
| item | name |
+ --------- + --------- +
| 1 | Columbus, OH |
| 2 | Columbus, OH |
| 2 | New York, NY |
| 2 | San Francisco, CA |
| 3 | Ft. Worth, TX |
| 3 | New York, NY |
+ --------- + --------- +
6 rows
EDIT: or if you want the data to be denormalized like this:
select
t1.item,
group_concat(t2.name)
from
table1 t1 join table2 t2 on (
t1.code = t2.code
or t1.code like concat(t2.code, ',%')
or t1.code like concat('%,', t2.code, ',%')
or t1.code like concat('%,', t2.code)
)
group by t1.item
order by t1.item
+ --------- + -------------------------- +
| item | group_concat(t2.name) |
+ --------- + -------------------------- +
| 1 | Columbus, OH |
| 2 | Columbus, OH,New York, NY,San Francisco, CA |
| 3 | Ft. Worth, TX,New York, NY |
+ --------- + -------------------------- +
3 rows
source to share
And here we see a great example of why using comma separated lists in database fields is a bad idea. They are more difficult to manipulate than a proper relational table.
With that in mind, I would consider splitting the code into multiple records first, then doing an easy set-based replacement and then putting them back in. Essentially:
-
Using the split function , create a temporary table tmp1 with 1 entry for each item / code pair.
-
Then UPDATE on tmp1.code from tmp1 joined to table 1.
-
Finally, use GROUP_CONCAT to get the names back together.
source to share