mysql如何用给定的一组值更新每行的列

Question

我希望从给定的一组值中更新表中每一行的值。例如：

id     name     code
---------------------
1      n1       
2      n2     
3      n3 

我有一个值数组' code -1‘、' code -2’、'code3‘，所以对于第一行，代码列的值将是数组中的' code -1'，第二行的代码列的值将是'code-2'，而对于代码列的第三行值将是数组中的'code-3’。最后一张表将如下所示：

id     name     code
---------------------
1      n1       code-1
2      n2       code-2
3      n3       code-3

如何在一个sql查询中做到这一点？

Answer 1

你可以这样做：

update table t
    set code = concat('code-', id)
    where id in (1, 2, 3);

如果代码没有真正绑定到If，则可以使用case

update table t
    set code = (case when id = 1 then 'code-1'
                     when id = 2 then 'code-2'
                     when id = 3 then 'code-3'
                end)
    where id in (1, 2, 3);

Answer 2

创建一个新表(temp)，以便使用如下模式保存数组

id       code
-------------
1       code-1
2       code-2
3       code-3

并使用以下update语句更新原始表

UPDATE original
SET code = temp.code
FROM original INNER JOIN temp
ON original.id = temp.id

Answer 3

如何识别表中的“第一行”？“第二排”怎么样？

为了获得UPDATE语句，我首先从SELECT语句开始，该语句返回要分配的新值和行。

您目前拥有的“数组”，如果将其转换为表，将是最简单的。如果您不想定义一个表并加载它，我们可以使用内联视图，但是语句的大小可能是一个限制。

例如，如果我们运行此查询：

  SELECT 1 AS rownum, 'code-1' AS code
  UNION ALL SELECT 2, 'code-2'
  UNION ALL SELECT 3, 'code-3'

我们返回结果集中的三行，可以像表一样引用。使用额外的UNION ALL SELECT来扩展它以添加额外的行。

为了从我们要更新的表中获得相应的rownum值，我们返回主键(或唯一的非空键)，并按我们想要的顺序返回这些键，因此第一行得到一行1，第二行得到一行2，等等：

  SELECT r.id
       , @rn := @rn + 1 AS rowumn
    FROM mytable r
   CROSS JOIN ( SELECT @rn := 0 ) i
   ORDER BY r.name, r.id

使用这两个结果，我们可以返回要更新的表，返回要分配给该行的新代码值，例如：

  SELECT t.id
       , t.name
       , t.code AS old_code
       , c.code AS new_code
       , o.rownum
    FROM ( SELECT 1 AS rownum, 'code-1' AS code
           UNION ALL SELECT 2, 'code-2'
           UNION ALL SELECT 3, 'code-3'
         ) c
    JOIN ( SELECT r.id
                , @rn := @rn + 1 AS rowumn
            FROM mytable r
            CROSS JOIN ( SELECT @rn := 0 ) i
            ORDER BY r.name, r.id
         ) o
      ON o.rownum = c.rownum
    JOIN mytable t
      ON t.id = o.id 

一旦我们满意地返回了想要更新的行以及我们要分配给每一行的新值，我们就可以通过用UPDATE替换SELECT ... FROM，并在语句末尾添加一个SET子句，将其转换为一个UPDATE语句。(如果我们有WHERE子句，则在WHERE子句之前)。

  UPDATE ( SELECT 1 AS rownum, 'code-1' AS code
           UNION ALL SELECT 2, 'code-2'
           UNION ALL SELECT 3, 'code-3'
         ) c
    JOIN ( SELECT r.id
                , @rn := @rn + 1 AS rowumn
            FROM mytable r
            CROSS JOIN ( SELECT @rn := 0 ) i
            ORDER BY r.name, r.id
         ) o
      ON o.rownum = c.rownum
    JOIN mytable t
      ON t.id = o.id 
     SET t.code = c.code

我就是这么做的。

如果“新代码”数组中的行数很大，我将创建一个表，并将数组值加载到表中。然后将内联视图查询c替换为对表的引用。

    FROM my_new_code_table c

或者，我可以把语句分解成可管理的部分。执行20段代码，然后对下一组20行：

       ( SELECT 21 AS rownum, 'code-21' AS code 
         UNION ALL SELECT 22, 'code-22'
         ... 
         UNION ALL SELECT 40, 'code-40'
       ) c