MySQL INSERT help

Hi, I have a question about a MySQL INSERT statement.

I've got a database that I need to update daily. There is a field that is unique to each record, lets call id the id field, now When I get the new data I want to do an INSERT INTO but instead of adding another entry if that entry already exists then I want to Update that entry. Is there a way I can tell MySql to do this?

Thanks...

 

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

Taken from : http://www.w3schools.com/sql/sql_update.asp

 

You can do this, but it will take a series of statements. Not sure what programming language you're using to interface with the database, but basically I'd do this:

UPDATE tableName
SET field1 = 'value1'
WHERE id = idValue

After you execute that statement, check the amount of rows that were affected. If there were 0 rows affected, then you'll know to perform the insert statement:

INSERT INTO tableName (id, field1)
VALUES (idValue, 'value1')

 

Yes, or you could run a select query first ....
"select * from table_name where id=id_value"

Then, if you are running PHP, use mysql_num_rows($resultset) to check the number of rows for this.

If this is 1 - Run update query
If this is 0 - Run insert query

Hope this helps!
dfsweb

 

use INSERT ... ON DUPLICATE KEY UPDATE syntax.

 

Sweet, thanks for all the help guys.. I'll try them out later tonight.

I did try the ON DUPLICATE KEY UPDATE syntax that would be perfect but my MySQL version is prior to 4.1 :-(

Thanks again.