mysql replace values in database

[dannyd]

I want to insert data into the database.

 

I have 3 values ($airtime, $length, $showid)

 

example of data to be inserted would be ->  (08-02-16 06:00, 20 , FFF-3300)

 

I'm pulling data from a file to insert into a database. However whenever the script runs I want to overwrite the data with the newest data in case a file was changed. How do i replace data in the database  with the newest data ?

 

 

 

[fenway]

INSERT INTO... ON DUPLICATE KEY UPDATE....

[toplay]

Or use REPLACE (instead of INSERT). See which is faster for you.

 

http://dev.mysql.com/doc/refman/5.0/en/replace.html

 

"The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed."

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

 

[fenway]

But REPLACE changes the UID, and issues an implicit DELETE... not good.

[dannyd]

If I use the replace clause then how would it work with a table that has no primary key ?

 

This is an example of data from the table

 

airtime                      length      creative

--------------------------------------------

08-02-20 06:00:00      200          SPO-3053

08-02-20 06:02:00        15          SPO-0134

08-02-20 06:02:15        15          SPO-2724

08-02-20 06:02:30    2829          LOC-2939

08-02-20 06:31:00      9            SPO-0135

08-02-20 06:31:09      20            SPO-0135

 

 

I guess time would be unique to the table row. Not sure how to use it though. Every time it overwrites it need needs to overwrite data specific to the date.

[dannyd]

How would I specify in a replace, to replace data according to the airtime field ?

 

Would this be correct syntax or would it work :

 

$sql ="REPLACE INTO asrunlogs (airtime,length,creative) values ('$datetime', '$length','$creative') WHERE airtime='$date'";

 

[fenway]

REPLACE won't do anything without any unique/primary index.

[toplay]

But REPLACE changes the UID, and issues an implicit DELETE... not good.

Yes is does a delete and insert. If you're using an auto_increment column as the primary key it will give a new unique value if the existing key value isn't specified, otherwise it will be similar to an update. I didn't want to assume it was an auto_increment column.

 

dannyd, MySQL requires a primary key. It doesn't  have to be an auto_increment, and a date/time could work provided that it will be unique. However, a date/time column is not usually used as a primary key (maybe as a secondary index with duplicates allowed).

 

You should think about what field would be unique for the info you're trying to save, and use that as the primary key.

 

 

 

 

 

 

 

[fenway]

The "not good" is for at least two reasons: one, not all users have delete permissions; two, if this UID is used as a FK in a MyISAM table, it will break the relationship.