mysql if dupe update one column if lower

[jonciukas]

ok another weird question from me btw using mysql 4.0.2 but upgradin soon so if ya help plz specify version if pos
ok here's the deal i have a db all set but sometimes i insert 1000s of entries at a time and most are dupes but is there a way that if the entry is a dupelicate on one column it would check if the incoming row has a lower value there and if it does then update it or if the current value is N/A and the new one has something then update it
i was looking on inserts and on duplicate but seems it only starts in the newest versions but also if using that not sure how to check and compare the entries of the current and incoming and then use only if incoming is lower etc..
hopefully someone can understand the problem i know it's a little jibberish sry :P just trying to learn hehe

thx for any suggjustions

[jonciukas]

bumping this one up once hopefully someone can help me out if not this is about the last place to look for me atm hehe

[r937]

doesn't make any more sense now that it did a few weeks ago

maybe you could take the time and explain it more clearly, perhaps with actual sample rows of data to illustrate what you want to do

[jonciukas]

ok say a have few rows in the db

name size time

hi 5 13
book 1 798
test 3 456

and say i run a insert cmd it contains name hi which is a dupe but instead of just rejecting it if dupe it would have to check if the time of the incoming insert is less than the one on file and if it's lower than change it, if it's bigger than reject it something along those lines

[r937]

"something along those lines" is not enough of a spec for anything related to computers

[jonciukas]

well take those 4 words away and thats what i want lol

[r937]

say i run a insert cmd it contains name hi which is a dupe but instead of just rejecting it if dupe it would have to check if the time of the incoming insert is less than the one on file ...

okay, let me see if i have this straight

youo anticipate a "dupe" which can only mean that there's already a row in the table with the key value that you're trying to insert

now, the time of the incoming insert, wouldn't it have to be greater than the time of the one that's already in the table?

... and if it's lower than change it, if it's bigger than reject it

well, even assuming you also were inserting the timestamp to go along with it, so that inserting a value "before" the one that's already there, the question now becomes, change what? change the one that's already there, or change the incoming one? and change it how?

can you see now what i mean about taking the time to clarify what you want?

please, give us some better sample data

show us before and after values

i'm sure we can work something out

[jonciukas]

yea i guess my example wasn't really clean i'm inserting a site in seconds since a certain date so if the number is lower it's earlier if it's higher it's older thats what the numbers in the example are anyways it's a timestamp and if the on incoming is earlier than the on on db i want to change the one on the db to the new on if it's a dupe
the title is the unique index and rest is just info
hopefully thats clear enough hehe

[r937]

it's still pretty incoherent (you should break your thoughts out into individual sentences, don't run them all together in a stream of consciousness manner) but i think i see what you're after

look up the ON DUPLICATE KEY UPDATE syntax option of the INSERT statement

[Roger Ramjet]

And take a look at the control flow functions . These include If and Case statements for conditional processing such as I understand yours to be.

[jonciukas]

yea roger think you got closest btw r937 i said in my first thing that i loked at on duplicate but my mysql version was too low but i'd probably have to combine on dupe and these flow thing swill have to read up on this since i'm really lost on flow functions atm lol ty though