Multiple UPDATES vs. One UPDATE?

DoubleDee · December 16, 2012, 9:24pm

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

DoubleDee · December 16, 2012, 10:14pm

Here is a copy of my actual query…


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

r937 · December 16, 2012, 11:34pm

yes

DoubleDee · December 16, 2012, 11:52pm

**Edited samples below…

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…


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…


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

r937 · December 17, 2012, 12:31am

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

DoubleDee · December 18, 2012, 4:04am

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…


$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.


	// 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…


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

r937 · December 18, 2012, 5:05am

http://dev.mysql.com/doc/refman/5.0/en/update.html

[indent]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:

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

[/indent]

there’s a word for people like this, and that word is: wrong

um, not really, because it made enough sense before

DoubleDee · December 19, 2012, 2:39am

r937:

http://dev.mysql.com/doc/refman/5.0/en/update.html

[indent]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:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
[/indent]

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…


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

r937 · December 19, 2012, 3:12am

and what happened when you tested that?

DoubleDee · December 19, 2012, 4:05am


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

r937 · December 19, 2012, 7:41am

you somehow snuck an illegal equals sign in there since i last saw this query in post #9

DoubleDee · December 20, 2012, 2:16am

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…


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

r937 · December 20, 2012, 5:17pm

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

there’s no need

try just a simple WHERE clause –

UPDATE private_msg_recipient
   SET read_on = NOW()
     , updated_on = NOW()
 WHERE member_id_to = 19
   AND deleted_on IS NULL