SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Multiple UPDATES vs. One UPDATE??

    My website has a Private Message center which works in a manner very similar to how Yahoo Mail works.

    When a User views his/her Inbox, next to each Message is a check-box.

    By checking certain Messages in the Inbox, then choosing an "action" (e.g. Mark as Unread) and then "Go", the script will update the Messages accordingly.

    Now on to my question...

    So currently, when the user submits the Form, I store all Messages that he/she checked in an Array. From there, I LOOP through the Array values, and run a separate UPDATE query for each Message that was chosen.


    Would it be better to eliminate the ForEach Loop, and somehow run a SINGLE UPDATE query??


    From my research online, some people say one UPDATE is better, and yet others say multiple UPDATES are better?!

    What are the Pros and Cons of each approach?


    Also, if eliminating my Loop and having one query is the way to go, how exactly would I take all of the Messages in my Array and stick them in One Query??

    Thanks,


    Debbie

  2. #2
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Here is a copy of my actual query...

    Code MySQL:
    UPDATE private_msg_recipient
    SET read_on=NULL,
    	updated_on=NOW()
    WHERE member_id_to=?
    AND message_id=?
    LIMIT 1


    Since I would be updating all Messages for a given User, the "Member ID" would be the same. However, for the "Message IDs", I would need to somehow take them from my Array and stick them into this one query, assuming that it is better to have just one UPDATE query?!

    Sincerely,



    Debbie

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Would it be better to eliminate the ForEach Loop, and somehow run a SINGLE UPDATE query??
    yes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    **Edited samples below...

    Quote Originally Posted by r937 View Post
    yes
    Originally I was going to use an Array, but I have since decided that it would make more sense get the records I need from a SELECT query.

    Here is my UPDATE query...
    Code:
    UPDATE private_msg
    SET read_on=NULL,
    	updated_on=NOW()
    WHERE member_id=?
    AND message_id=?
    LIMIT 1";

    Here is an example of my SELECT query...
    Code:
    SELECT message_id
    FROM private_msg
    WHERE member_id = ?
    AND deleted_on IS NULL
    ORDER BY sent_on DESC';

    I tried looking online for examples, but haven't found anything (surprisingly).

    How would I take the listing of Private Messages in a User's Inbox and apply that results-set to my UPDATE query?

    Help on doing this would be appreciated!

    Sincerely,


    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    not completely sure what you're doing with your arrays and SELECTs and so on...

    ... but it sounds like you want to use a joined update

    there's a nice example in the manual
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not completely sure what you're doing with your arrays and SELECTs and so on...

    ... but it sounds like you want to use a joined update

    there's a nice example in the manual
    I read the Manual, and I looked in the manual for your "keyword hint", and I Googled your "keyword hint" and found nothing?!


    What I wanted to do last night using the SELECT is now out.

    Back to what I originally wanted to do is this...


    - A User goes into her Inbox and checks the Check-box next to several Private Messages. She then choose the action "Mark as Unread" in a drop-down above the Inbox, and chooses "Go".

    - My Form saves those checked Messages in an array like this...
    Code:
    $testArray:
    
    array
      1 => int 52
      2 => int 51
      3 => int 49
      4 => int 39
      5 => int 38
      6 => int 10
      7 => int 8
      8 => int 6
      9 => int 5
      10 => int 2
      11 => int 1
    ...where the value to the right in the "$pmID".

    - Once the Form is submitted, I need to update the records for all of those PMs.

    - Originally is used a ForEach loop to iterate through each item in the array, and ran an UPDATE query on EACH PASS.
    Code:
    	// Update Selected Messages.
    	foreach($_POST['msgArray'] as $msgID => $msgValue){
    
    	// Build query.
    	$q1 = "UPDATE private_msg_recipient
    			SET read_on=NULL,
    				updated_on=NOW()
    			WHERE member_id_to=?
    			AND message_id=?
    			LIMIT 1";
    - To be more efficient - based on what others including you advise - I want to run a SINGLE UPDATE query on ONE PASS.

    - To do that, I need this UPDATE query to run on all Records that fall in this list (52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1).


    I tried doing something like this last night, but it only updated one record....
    Code:
    UPDATE private_msg_recipient
    SET read_on=NULL,
    updated_on=NOW()
    WHERE member_id_to = 19
    AND message_id = 52 OR message_id = 51

    I have had a hard time finding an answer, because I didn't know what I was searching for online.

    Some people online have said MySQL doesn't let you update multiple records in the same table.

    I thought something like "IN" would work, but that doesn't seem to be the case.

    Hope my inquiry makes more sense now?


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I read the Manual, and I looked in the manual for your "keyword hint", and I Googled your "keyword hint" and found nothing?!
    http://dev.mysql.com/doc/refman/5.0/en/update.html

    You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.8.2, “JOIN Syntax”. Here is an example:

    Code:
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;


    Quote Originally Posted by DoubleDee View Post
    Some people online have said MySQL doesn't let you update multiple records in the same table.
    there's a word for people like this, and that word is: wrong


    Quote Originally Posted by DoubleDee View Post
    Hope my inquiry makes more sense now?
    um, not really, because it made enough sense before

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

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    http://dev.mysql.com/doc/refman/5.0/en/update.html

    You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.8.2, “JOIN Syntax”. Here is an example:

    Code:
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;
    You keep mentioning Multiple Tables and Joins, but I also mentioned that I don't think merging a SELECT and an UPDATE is the path I want to take after some consideration into my issue.

    That being said, how can I use an Array or List to dictate which records get updated?

    I thought that you could do something like...
    Code:
    UPDATE private_msg_recipient
    SET read_on=NULL,
    updated_on=NOW()
    WHERE member_id_to IN (52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1)

    (BTW, I am using Prepared Statements exclusively, so that might impact how I could do something like above...)

    Sincerely,


    Debbie

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I thought that you could do something like...
    Code:
    UPDATE private_msg_recipient
    SET read_on=NULL,
    updated_on=NOW()
    WHERE member_id_to IN (52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1)
    and what happened when you tested that?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and what happened when you tested that?
    Code:
    Error
    
    SQL query:
    
    UPDATE private_msg_recipient SET read_on = NOW( ) ,
    updated_on = NOW( ) WHERE member_id_to =19 AND message_id = IN ( 52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1 )
    
    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1)' at line 5

    Debbie

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you somehow snuck an illegal equals sign in there since i last saw this query in post #9
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you somehow snuck an illegal equals sign in there since i last saw this query in post #9
    More like I had an equal sign in my code and must have typed things up incorrectly here.

    So I guess using "IN ( 52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1 )" is okay?


    Also, back to my original question on which I am still not understanding your references to the Manual...

    If I needed to, could I use a SELECT query to define the allowable values in the WHERE part of my UPDATE statement?

    Almost like a sub-query?

    I tried this, but MySQL didn't like it...

    Code:
    UPDATE private_msg_recipient
    SET read_on=NOW(),
    updated_on=NOW()
    WHERE member_id_to = (SELECT member_id_to
    FROM private_msg_recipient
    WHERE member_id_to=19
    AND deleted_on IS NULL)
    Sincerely,


    Debbie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Also, back to my original question on which I am still not understanding your references to the Manual...
    the reference was to a joined update where you update rows in one table from matching rows in a second table

    obviously, if you have only one table under consideration, you'll use an even simpler query

    Quote Originally Posted by DoubleDee View Post
    If I needed to, could I use a SELECT query to define the allowable values in the WHERE part of my UPDATE statement?
    there's no need

    try just a simple WHERE clause --
    Code:
    UPDATE private_msg_recipient
       SET read_on = NOW()
         , updated_on = NOW()
     WHERE member_id_to = 19
       AND deleted_on IS NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •