Insert if one column doesn't match, otherwise update

[silkfire]

I have a table with a some data. The first column is a normal id column (Unique index, auto increment, starting from 1) and the second is a serial number. Rest are data columns.

 

I've tried with the DUPLICATE KEY INSERT and didn't work.

 

What I want is - if the serial number exists in the table - update all other fields (except id), if the serial number doesn't exist - make an INSERT with all the data.

 

Is this possible without doing a SELECT first?

 

And is it possible to extend it to check if 2 or more fields (which I specify in query) are the same - then update all other fields, else INSERT?

[monkeytooth]

I'm not 100% understanding what your saying. But chances are in the overall you would be better to do a SELECT to return a count.. based on that count a simple if-else to either update or insert. But generally speaking in order to find out if there is something to update you have to first search for it. Though I am sure there is some super elaborate way to do what your saying without directly selecting anything out right. But for me, in a simple case like this sounds its likely easier to just go a head and select then count then do upon that count what need be done. If you want to avoid duplicates in the long run, try

 

SELECT DISTINCT(column_name) FROM table_name WHERE column_name = 'value'

 

DISTINCT runs a smidge bit slower as it will crawl through the entire DB of records on that particular table but it will or should return only one result every time in a matter of speaking at least.

[monkeytooth]

I think I might retract my previous statement, although I think what I said in the long run may be a bit easier. Despite the extra query made to achieve the goal. I have to say though I don't typically use this myself (maybe I should start)..

 

This might be a good reference for ya.

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

 

its not specific to what you want to do, but I think its a good starting place