MYSQL UPDATE查询使用表达式

Question

我有一个表，比如table1列id(INT)， tag(VARCHAR)，tag2(VARCHAR)，var1(VARCHAR)，var2(VARCHAR)和external_tags(TINY INT)． 只有id, tag和external_tags字段中的数据。我必须更新该表中的每一行，以便将标记字段中的值拆分为tag2和var1字段。例如，有许多像

这样的行| id | tag | tag2 | var1 | external_tags |var2

| 1 | school_signup_xxx |NULL |NULL | 0 |NULL

| 2 | course_enroll_213 |NULL |NULL | 0 |NULL

| 3 | course_enrollment _556 |NULL |NULL | 0 |NULL

| 4 | automation_notify_bounced_111_222 |NULL |NULL | 0 |NULL

| 5 | automation_notify_bounced_666_223 |NULL |NULL | 0 |NULL

| 6 | automation_notify_bounced_426_253 |NULL |NULL | 0 |NULL

| 7 | registered_livestream_co_host_111_schedule_444 |NULL |NULL | 0 |NULL

| 8 | registered_livestream_co_host_632_schedule_234 |NULL |NULL | 0 |NULL

| 9 | attended_recurring_live_class_868_rec_schedule_151 |NULL |NULL | 0 |NULL

| 10 | attended_recurring_live_class_545_rec_schedule_747 |NULL |NULL | 0 |NULL

| 11 | manual_in |NULL |NULL | 1 |NULL

| 12 | test tag |NULL |NULL | 1 |NULL

| 13 | test tag abc |NULL |NULL | 1 |NULL

我想更新表table1中的上述行，如下所示

| id | tag | tag2 | var1 | external_tags |var2

| 1 | school_signup_xxx | school_signup | xxx | 0 |NULL

| 2 | course_enroll | 213 | course_enroll | 213 | 0 |NULL

| 3 | course_enroll | 556 | course_enroll | 0 |NULL

| 4 | automation_notify_bounced_111_222| automation_notify_bounce | 111 | 0 |222

| 5 | automation_notify_bounced_666_223| automation_notify_bounce | 666 | 0 |223

| 6 | automation_notify_bounced_426_253| automation_notify_bounce | 426 | 0 |253

| 7 | registered_livestream_co_host_111_schedule_444 | registered_livestream_co_host | 111 | 0 |444

| 8 | registered_livestream_co_host_632_schedule_234 | registered_livestream_co_host | 632 | 0 |234

| 9 | attended_recurring_live_class_868_rec_schedule_151 | attended_recurring_live_class | 868 | 0 |151

| 10 | attended_recurring_live_class_545_rec_schedule_747 | attended_recurring_live_class | 545 | 0 |747

| 11 | manual_in | manual_in |NULL | 1 |NULL

| 12 | test tag | test tag |NULL | 1 |NULL测试标签abc |测试标签abc |NULL | 1 |NULL+----+-------------------+------+------+---------------+---------------+-----------------------------------------------

有6个案例

1. 如果external_tags = 0且tag = school_signup_{var1value}则将tag2更新为school_signup，将var1更新为var1value
2. 如果external_tags = 0且tag = course_enroll _{var1value}则将tag2更新为course_enroll，将var1更新为var1value
3. 如果external_tags = 0且tag = automation_notify_bounced_{var1value}_{var2value}则将tag2更新为automation_notify_bounce, var1更新为var1value, var2更新为var2value
4. 如果external_tags = 0且tag = registered_livestream_co_host_{var1value}schedule{var2value}则将tag2更新为registered_livestream_co_host，将var1更新为var1value，将var2更新为var2value
5. 如果external_tags = 0且tag = attended_recurring_live_class_{var1value}rec_schedule{var2value}则将tag2更新为attended_recurring_live_class，将var1更新为var1value，将var2更新为var2value
6. 如果external_tags = 1，则更新tag2为标签值

如何使用6个更新查询更新上述情况?

请回复MYSQL查询。

Answer 1

您可以将一个查询连接到表中，该查询返回6行，每种情况1行，并使用MySql's string functions提取列tag中的字符串片段，这将更新其他列:

UPDATE tablename t
INNER JOIN (
SELECT 0 external_tags, 'school_signup' tag UNION ALL
SELECT 0, 'course_enroll' UNION ALL
SELECT 0, 'automation_notify_bounced' UNION ALL
SELECT 0, 'registered_livestream_co_host' UNION ALL
SELECT 0, 'attended_recurring_live_class' UNION ALL
SELECT 1, NULL 
) e ON t.external_tags = e.external_tags AND (e.tag IS NULL OR INSTR(t.tag, CONCAT(e.tag, '_')) = 1)
SET t.tag2 = COALESCE(e.tag, t.tag),
t.var1 = CASE 
WHEN t.external_tags = 1 THEN t.var1
ELSE SUBSTRING_INDEX(SUBSTR(t.tag, CHAR_LENGTH(e.tag) + 2), '_', 1)
END,
t.var2 = CASE 
WHEN t.external_tags = 1 OR INSTR(SUBSTR(t.tag, CHAR_LENGTH(e.tag) + 2), '_') = 0 THEN t.var2
ELSE SUBSTRING_INDEX(SUBSTR(t.tag, CHAR_LENGTH(e.tag) + 2), '_', -1)
END