mysql - UPDATE new_column 而不是 SELECT new_column

Question

我目前有这段代码:

SELECT x.id, x.company, x.dt, x.price, x.ctcy, (x.price - y.price)/y.price AS 'change'
FROM 
(
    SELECT a.id AS aid, MAX(b.id) AS aPrevid
    FROM price_practice a
    INNER JOIN price_practice b
    WHERE a.id > b.id
    AND a.company = b.company
    GROUP BY a.id
) Sub1
INNER JOIN price_practice x ON Sub1.aid = x.id
INNER JOIN price_practice y ON Sub1.aPrevid = y.id
ORDER BY x.id DESC

它返回:

 id   company       dt       price  ctcy      change
'35', 'Citi', '2015-07-14', '0.59', NULL, '-0.180556'
'34', 'Citi', '2015-07-13', '0.72', NULL, '-0.088608'
'33', 'Citi', '2015-07-10', '0.79', NULL, '-0.048193'
'32', 'Citi', '2015-07-09', '0.83', NULL, '-0.126316'
'31', 'Citi', '2015-07-08', '0.95', NULL, '-0.059406'
'30', 'Amex', '2015-07-14', '6.01', NULL, '0.022109'
'29', 'Amex', '2015-07-13', '5.88', NULL, '0.059459'
'28', 'Amex', '2015-07-10', '5.55', NULL, '0.037383'
'27', 'Amex', '2015-07-09', '5.35', NULL, '0.022945'
'26', 'Amex', '2015-07-08', '5.23', NULL, '0.242280'
'25', 'Citi', '2015-07-07', '1.01', NULL, '0.147727'
'24', 'Citi', '2015-07-06', '0.88', NULL, '-0.120000'
'23', 'Citi', '2015-07-03', '1.00', NULL, '-0.090909'
'22', 'Citi', '2015-07-02', '1.10', NULL, '0.100000'
'20', 'Amex', '2015-07-07', '4.21', NULL, '-0.284014'
'19', 'Amex', '2015-07-06', '5.88', NULL, '0.176000'
'18', 'Amex', '2015-07-03', '5.00', NULL, '-0.019608'
'17', 'Amex', '2015-07-02', '5.10', NULL, '0.020000'

我不想在 change 列中显示我计算的新数据，而是想将它保存 (UPDATE) 在空列 ctcy。毫不奇怪，将 SELECT 替换为 UPDATE 会得到第 1 行的 Error Code: 1064。那么我该怎么做呢？

Answer 1

UPDATE price_practice x
INNER JOIN
(
    SELECT a.id AS aid, MAX(b.id) AS aPrevid
    FROM price_practice a
    INNER JOIN price_practice b
    WHERE a.id > b.id
    AND a.company = b.company
    GROUP BY a.id
) Sub1 ON Sub1.aid = x.id
INNER JOIN price_practice y ON Sub1.aPrevid = y.id
SET x.ctcy = (x.price - y.price)/y.price