Change each record in a table with no primary key?

Question

I have a table in a database that represents dates textually (i.e. "2008-11-09") and I would like to replace them with the UNIX timestamp. However, I don't think that MySQL is capable of doing the conversion on its own, so I'd like to write a little script to do the conversion. The way I can think to do it involves getting all the records in the table, iterating through them, and updating the database records. However, with no primary key, I can't easily get the exact record I need to update.

Is there a way to get MySQL to assign temporary IDs to records during a SELECT so that I refer back to them when doing UPDATEs?

Answer 1

If for some reason you do have to iterate (the other answers cover the situation where you don't), I can think of two ways to do it (these aren't MySQL-specific):

1. Add a column to the table that's an auto-assigned number. Use that as the PK for your updates, then drop the column afterwards (or just keep it around for future use).

2. In a table with no defined PK, as long as there are no exact duplicate rows, you can use the entire row as a composite PK; just use every column in the row as your distinguishing characteristic. i.e., if the table has 3 columns, "name", "address", and "updated", do the following:

   UPDATE mytable SET updated = [timestamp value] WHERE name = [name] AND address = [address] AND timestamp = [old timestamp]

Many data access frameworks use this exact strategy to implement optimistic concurrency.

Answer 2

No, you should be able to do this with a single update statement. If all of the dates are yyyy-mm-dd and they are just stored in some sort of text column instead of DATETIME, you can just move the data over. SQL would be like:

ALTER TABLE t ADD COLUMN dates DATETIME;
UPDATE t set t.dates=t.olddate;

This shouldn't be dependent on a PK because MySQL can scan through each row in the table. The only time PK's become an issue is if you need to update a single row, but the row may not be unique.

Answer 3

Does this not do it?

UPDATE
  MyTable
SET
  MyTimeStamp = UNIX_TIMESTAMP(MyDateTime);

Answer 4

You can generate values during a SELECT using the MySQL user variables feature, but these values do not refer to the row; they're temporary parts of the result set only. You can't use them in UPDATE statements.

SET @v := 0;
SELECT @v:=@v+1, * FROM mytable;

Here's how I'd solve the problem. You're going to have to create another column for your UNIX timestamps anyway, so you can add it first. Then convert the values in the old datetime column to the UNIX timestamp and place it in the new column. Then drop the old textual datetime column.

ALTER TABLE mytable ADD COLUMN unix_timestamp INT UNSIGNED NOT NULL DEFAULT 0;

UPDATE mytable
SET unix_timestamp = UNIX_TIMESTAMP( STR_TO_DATE( text_timestamp, '%Y-%m-%d' ) );

ALTER TABLE mytable DROP COLUMN text_timestamp;

Of course you should confirm that the conversion has been done correctly before you drop the old column!

See UNIX_TIMESTAMP() and STR_TO_DATE()