I currently have two tables, which aren't joined.
我目前有两个表,没有加入。
tbl_prod
cat_ids prod_txt
1,2,3 some text
tbl_cat
category_id cat_name
1 A
2 M
3 P
I need to perform a query which will update the contents of the 'cat_ids' column so the contents looks like this:
我需要执行一个查询,它将更新'cat_ids'列的内容,以便内容如下所示:
tbl_prod
cat_ids prod_txt
A,M,P some text
Is this even possible in mySQL, or might I be better off creating a script to do this. Thanks
这在mySQL中是否可行,或者我最好创建一个脚本来执行此操作。谢谢
1 个解决方案
#1
1
Correct way:
Normalize your tables. Storing foreign keys as csv column is really bad idea.
规范化表格。将外键存储为csv列是非常糟糕的主意。
Workaround:
I don't have control of the output, so normalizing isn't really an option here
我无法控制输出,因此规范化在这里不是一个选择
SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids,
MAX(prod_txt) AS prod_txt
FROM tbl_prod AS tp
JOIN tbl_cat AS tc
ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
GROUP BY cat_ids;
Output:
╔══════════╦═══════════╗
║ cat_ids ║ prod_txt ║
╠══════════╬═══════════╣
║ A,M,P ║ some text ║
╚══════════╩═══════════╝
Update:
UPDATE tbl_prod t
join (SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids_rep,
MAX(tp.cat_ids) AS cat_ids
FROM tbl_prod AS tp
JOIN tbl_cat AS tc
ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
GROUP BY cat_ids
) AS sub
ON t.cat_ids = sub.cat_ids
SET t.cat_ids = sub.cat_ids_rep;
#1
1
Correct way:
Normalize your tables. Storing foreign keys as csv column is really bad idea.
规范化表格。将外键存储为csv列是非常糟糕的主意。
Workaround:
I don't have control of the output, so normalizing isn't really an option here
我无法控制输出,因此规范化在这里不是一个选择
SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids,
MAX(prod_txt) AS prod_txt
FROM tbl_prod AS tp
JOIN tbl_cat AS tc
ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
GROUP BY cat_ids;
Output:
╔══════════╦═══════════╗
║ cat_ids ║ prod_txt ║
╠══════════╬═══════════╣
║ A,M,P ║ some text ║
╚══════════╩═══════════╝
Update:
UPDATE tbl_prod t
join (SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids_rep,
MAX(tp.cat_ids) AS cat_ids
FROM tbl_prod AS tp
JOIN tbl_cat AS tc
ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
GROUP BY cat_ids
) AS sub
ON t.cat_ids = sub.cat_ids
SET t.cat_ids = sub.cat_ids_rep;