cant update table from another table

[manhattes]

I am trying to set a column equal to the column in another table where the date matches. I seem to have a problem in my query and I have tried multiple methods. Can someone tell me where the mistake is?

 

$query2 = "UPDATE  a

  SET a.CalDatePrice = b.Close

  FROM ". $symbol ." b

  INNER JOIN CleanedCalendar a

  ON a.BuyDate = b.Date";

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM CLBS b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Dat' at line 3

[Barand]

You are mixing DELETE, UPDATE and SELECT syntaxes in the vain hope of getting something to work. Have you tried reading the manual?

 

http://dev.mysql.com/doc/refman/5.6/en/update.html

 

You'll find it should be like this

UPDATE $symbol b
INNER JOIN CleanedCalendar a
  ON a.BuyDate = b.Date
SET a.CalDatePrice = b.Close

[manhattes]

 

You are mixing DELETE, UPDATE and SELECT syntaxes in the vain hope of getting something to work. Have you tried reading the manual?

 

http://dev.mysql.com/doc/refman/5.6/en/update.html

 

You'll find it should be like this

UPDATE $symbol b
INNER JOIN CleanedCalendar a
  ON a.BuyDate = b.Date
SET a.CalDatePrice = b.Close

That gives me an internal server error.

 

Looking at the syntax page, it would appear it should be formatted like this:

 

$query2 = "UPDATE CleanedCalendar,  ". $symbol ."

   SET CleanedCalendar.CalDatePrice = ". $symbol .".Close

WHERE CleanedCalendar.BuyDate = ". $symbol .".Date";

 

However this also gives an internal Server error

[Barand]

You may need to put $symbol inside backticks in case it contains a reserved word or special characters. ie

... `$symbol` ...

[manhattes]

 

You may need to put $symbol inside backticks in case it contains a reserved word or special characters. ie

... `$symbol` ...

Still doesnt work. 

$symbol is only letters with no spaces

[manhattes]

Little background on the DB

Table1  is CleanedCalendar

Table2 is $symbol (there is a symbol table for each symbol which has 1 entry per day making Date a unique column)

 

I want to set the column CalDatePrice = to the price in the symbol table on the matched date.

[manhattes]

I can no longer open the table after I ran this query and it appeared to run correctly. :-(

 

$query2 = "UPDATE ". $symbol ." b

INNER JOIN CleanedCalendar a

ON a.BuyDate = b.Date

   SET a.CalDatePrice = b.Close";

Edited December 30, 2015 by manhattes