Change every record in a table without a primary key?
I have a table in a database that represents dates by text (eg "2008-11-09") and I would like to replace them with a UNIX timestamp. However, I don't think MySQL is capable of doing the conversion on its own, so I would like to write a little script to do the conversion. The way I can do this involves getting all the records in the table, iterating over them, and updating the database records. However, without the primary key, I cannot easily get the exact record that I need to update.
Is there a way to get MySQL to assign temporary IDs to records during SELECT so that I will fall back to them when I UPDATE?
source to share
If for some reason you have to iterate over the iterations (other answers cover the situation when you don't), I can imagine two ways to do it (they are independent of MySQL):
-
Add a column to the table with an automatic number. Use this as a PC for your updates, then drop the column (or just save it for later use).
-
In a table without a specific PK, if there are no exact duplicate rows, you can use the entire row as a composite PK; just use each column in a row as your differentiator. that is, if the table has 3 columns, "name", "address" and "update", do the following:
UPDATE mytable SET updated = [timestamp value] WHERE name = [name] AND address = [address] AND timestamp = [old timestamp]
Many data access systems use this exact strategy to implement optimistic concurrency.
source to share
No, you should be able to do this with a single update statement. If all dates are yyyy-mm-dd and they are just stored in some text column instead of DATETIME, you can just move the data around. The SQL will look like this:
ALTER TABLE t ADD COLUMN dates DATETIME;
UPDATE t set t.dates=t.olddate;
This should not be PC dependent because MySQL can scan every row in the table. The only time PK becomes an issue is if you need to update a single row, but the row might not be unique.
source to share
You can generate values at time SELECT
using the MySQL user variable function, but these values are not string specific; they are only temporary parts of the result set. You cannot use them in statements UPDATE
.
SET @v := 0;
SELECT @v:=@v+1, * FROM mytable;
This is how I solved the problem. You will have to create another column for UNIX timestamps anyway, so you can add that first. Then convert the values in the old datetime column to UNIX timestamp and put it in the new column. Then drop the old datetime text 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 must confirm that the conversion is correct before you delete the old column!
See UNIX_TIMESTAMP()
andSTR_TO_DATE()
source to share