How to add the result of a query into a column

Question

I have the following query which returns a column of values:

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;

How can I copy this column to an existing column in the same table?

Angel Politis · Answer 1 · 2018-01-11T08:05:36.487

I believe a subquery would do:

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

Edit:
^{(in response to this comment)}

After doing some research, I found that, even though the suggested solution above is valid SQL, it's not a supported way of updating a table in MySQL.

Information I gathered from various related posts here on Stack Overflow (such as this and this) suggests that MySQL restricts these types of UPDATE queries:

... 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.

Therefore, a viable alternative that helps avoid this arbitrary restriction set by MySQL is to use another subquery, this time in your FROM clause, as shown below:

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

Note: I would personally avoid using the SELECT * FROM IEX_Tick, especially if IEX_Tick has many columns. I good way to optimise the subquery would be to use only the column(s) needed.

I tried that but got the error "You can't specify target table 'IEX_Tick' for update in From clause. I've been searching and this seems to be a mysql thing — ajasif, Jan 02 '18 at 22:11
You are right @ajasif. Check out my edit. I did some research and found a solution that should be working for you — Angel Politis, Jan 11 '18 at 07:58

How to add the result of a query into a column

1 Answers1