-1

I am trying to convert datetime to timestamp format in my table 'mytable'. It gives me an error say: You can't specify target table 'mytable' for update in FROM clause. Was I wrong with update or converting?

UPDATE mytable
SET TimeStampIN = (SELECT UNIX_TIMESTAMP(STR_TO_DATE(timestamp, '%h:%i%p') FROM mytable)
lyhong
  • 947
  • 1
  • 13
  • 23
  • possible duplicate of [You can't specify target table 'NAME' for update in FROM clause](http://stackoverflow.com/questions/17742214/you-cant-specify-target-table-name-for-update-in-from-clause) – Maz I Feb 20 '14 at 09:52

1 Answers1

1

Use another subquery to overcome this restriction of MySQL

UPDATE mytable
SET TimeStampIN = 
(
  select * from 
  (
     SELECT UNIX_TIMESTAMP(STR_TO_DATE(timestamp, '%h:%i%p') FROM mytable
  ) x
)
juergen d
  • 201,996
  • 37
  • 293
  • 362