CODEWITHSUNDEEP
mysql
Gokulalakshmi
Gokulalakshmi

Reputation: 15

Updating a column shows syntax error in mysql

UPDATE datanyc_new t2
SET    Prevclose = t1.Last
FROM   datanyc t1
WHERE  t2.Symbol = t1.Symbol
AND    t2.`Timestamp` = '2020-01-03'
AND    t1.`Timestamp` = ( SELECT max( Timestamp ) FROM datanyc WHERE Timestamp= '2020-01-03')

This is the query I used.

enter image description here

Upvotes: 0

Views: 59

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Multiple-table syntax: for mysql is

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

https://dev.mysql.com/doc/refman/8.0/en/update.html

Assuming the question is tagged correctly try

UPDATE datanyc_new dn join datanyc d on dn.Symbol = d.Symbol
SET    Prevclose = d.Last
WHERE  dn.`Timestamp` = '2020-01-03'
  AND  d.`Timestamp` = ( SELECT max(Timestamp) FROM datanyc WHERE Timestamp= '2020-01-03');

I'm not seeing the point of the subquery or possibly it is incorrect, if timestamp column contains datetime and the intention is to get the last time for 2020-01-03 then the where is incorrect and will return null you may need WHERE date(timestamp) = '2020-01-03'

Upvotes: 1

Related Questions