SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert/Update query help.

    Hi all,
    Im using mysql 5.0. I am doing a insert/update query for a particular record. The situation here is, I'm doing a select query first to check whether the given record is present or not, my select query is-->

    Code MySQL:
    Select Req_Code,Expenditure_Code from Expenditure_Requisition_Detail Where Req_Code = 'R42141620'

    If the req_code value is not present in the table, i do an insert query as--->

    Code MySQL:
    INSERT INTO Expenditure_Requisition_Detail(Req_Code,Payment_By_Branch,Expenditure_Code,Estimated_Cost) VALUES (?,?,?,?)

    If the req_code value is present, i do an update query as---->

    Code MySQL:
    UPDATE Expenditure_Requisition_Detail SET Estimated_Cost = 2000.0 WHERE Payment_By_Branch = 'Lucknow' and Expenditure_Code = '41620101' and Req_Code = 'R42141620'

    All this is working fine, but now the problem here is, suppose the req_code is R42141620 and i select a new Expenditure_code(there are many expenditure_code) value 41620105, for the same req_code as compared to 41620101 in the above example, i want a duplicate req_code to be enterred with the new expenditure_code value without changing the existing set up.

    The output should be something like this---->

    Code:
    Req_code      expenditure_code    amount    destination   amount_paid
    R42141620	41620101	  10000	    Lucknow	 0
    Here if the req_code value is R42141620 and expenditure_code value is 41620101, if i add new amount for the same req_code and expen_code then the value should be updated, and if the req_code is same but the expenditure_code value is say 41620105 then an insert should happen and the result should be something like this---->

    Code:
    Req_code      expenditure_code    amount    destination   amount_paid
    R42141620	41620101	  10000	    Lucknow	   0
             
    R42141620	41620105	  12000	    Lucknow	   0
    P.S: I understand i have to do a on duplicate key update but i dont want to use it since i dont want the query specific to mysql. I hope i conveyed myself right.

    Any help here is appreciated.

    regards,
    ajos777

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ajos777 View Post
    i dont want to use it since i dont want the query specific to mysql.
    what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what was your question again?
    Im sorry for that. My question is how do i insert a new row when the req_code is same and expenditure_code is different. For example.

    Code:
    Req_code      expenditure_code    amount    destination   amount_paid
    R42141620	41620101	  10000	    Lucknow	   0
    Here, when a req_code is not present the insert query inserts a row with the req_code and expenditure_code.

    And again if a duplicate entry comes for the same req_code and expenditure_code then the update query just updates the row instead of a duplicate row.

    But for the same req_code, if a different expenditure_code comes then a new row with the same req_code with different expenditure_code value should be inserted, something like this...

    Code:
    Req_code      expenditure_code    amount    destination   amount_paid
    R42141620	41620101	  10000	    Lucknow	   0
             
    R42141620	41620105	  12000	    Lucknow	   0
    ive successfully done this for new(insert) and existing(update). I hope im clear this time.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    my advice would be to declare the primary key on the two columns as a composite primary key

    then do your INSERT statement first

    if it succeeds, you're finished

    if it fails on a "duplicate key" error, that means the PK already exists, so then issue the UPDATE statement

    depending on the volumes of expected inserts/updates, you might want to try it in the other sequence

    in other words, if you expect most key pairs to exist, issue the UPDATE first, and if it fails, then issue the INSERT

    in both cases, you really do not need to do the SELECT first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    my advice would be to declare the primary key on the two columns as a composite primary key

    then do your INSERT statement first

    if it succeeds, you're finished

    if it fails on a "duplicate key" error, that means the PK already exists, so then issue the UPDATE statement

    depending on the volumes of expected inserts/updates, you might want to try it in the other sequence

    in other words, if you expect most key pairs to exist, issue the UPDATE first, and if it fails, then issue the INSERT

    in both cases, you really do not need to do the SELECT first
    O thanks for the advice. I completely agree with you, infact this is what i did
    previously, where my req_code was primary key(since this will be unique). But the expenditure_code wont be primary key coz this is not unique. But now the requirement is such that i had to remove the primary key constraint coz the req_code can have multiple records but with varied expenditure_code. Say if the req_code is 123 and the expenditure_code is 789, i will only do an update(the next time for the same req_code and expend_code) if this 2 values are constant. If the req_code remains 123 but the expenditure_code becomes 456 then a new row will come with the same req_code but with different expenditure_code.

    And hence my this approach.

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...i managed to solve the issue with some logical statements in java. Just checked for some logic present, accordingly made the insert/update logic work.

    Thanks rudy for the kind advice on how to insert/update technique.

    regards,,
    ajos777


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
  •