java - 如何将查询结果添加到列中

Question

我有以下查询返回一列值:

SELECT CONCAT(
  from_unixtime(lastSaleTime/1000, '%Y-%d-%m %h:%i:%s.'),
  CAST(EXTRACT(MICROSECOND FROM from_unixtime(lastSaleTime/1000))/1000 
  AS SIGNED)
) FROM IEX_Tick;

如何将此列复制到同一表中的现有列？

Answer 1

我相信子查询可以:

UPDATE IEX_Tick SET SomeColumn = (
   SELECT CONCAT(
       from_unixtime(lastSaleTime/1000, '%Y-%d-%m %h:%i:%s.'),
       CAST(EXTRACT(MICROSECOND FROM from_unixtime(lastSaleTime/1000))/1000 AS SIGNED)
   ) FROM IEX_Tick;
)

---

编辑:
^{(回应 this comment)}

经过一些研究，我发现，尽管上面建议的解决方案是有效的 SQL ，它不是更新表的受支持方式 MySQL .

我从 Stack Overflow 上的各种相关帖子(例如 this 和 this )收集的信息表明 MySQL限制这些类型的 UPDATE查询:

... because your update could be cyclical… what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.

因此，一个可行的替代方案有助于避免 MySQL 设置的这种任意限制。是使用另一个子查询，这次在你的FROM子句，如下图:

UPDATE IEX_Tick SET SomeColumn = (
   SELECT CONCAT(
       from_unixtime(lastSaleTime/1000, '%Y-%d-%m %h:%i:%s.'),
       CAST(EXTRACT(MICROSECOND FROM from_unixtime(lastSaleTime/1000))/1000 AS SIGNED)
   ) FROM (
       SELECT * FROM IEX_Tick AS SomeName
   );
)

注意:我个人会避免使用 SELECT * FROM IEX_Tick ，特别是如果 IEX_Tick有很多列。优化子查询的好方法是仅使用所需的列。