SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql if dupe update one column if lower

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    "something along those lines" is not enough of a spec for anything related to computers

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well take those 4 words away and thats what i want lol

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jonciukas
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    marijampole
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •