UPDATE new_column instead of SELECT new_column

I currently have this code:

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

It returns:

 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'

Rather than displaying the new data I calculate in the change column, I want to save it (UPDATE) in the the empty column ctcy. Not surprisingly replacing SELECT with UPDATE gives Error Code: 1064 for line 1. So how do I do it?


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.