MySQL 问题 : adding the result of a query to a column in the same table

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;

此代码返回一列值，我想将所有这些数据添加到我创建的新列中。这是上次建议的:

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;
)

但是我收到错误消息“您无法在 FROM 子句中为更新指定目标表‘IEX_Tick’。我查了一下，我们已经尝试了一些解决方法，例如:

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 RRR;
)

但是还是不行

Answer 1

简单地避开子查询:

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