Using multiple UPDATE in MySQL

Question

I'm trying to "force" this code to run through every line and update every line that matches the statement above..

if(isset($itemID1) && isset($itemID2) && isset($itemID3) && isset($itemID4) && isset($itemID5) && isset($itemID6) && isset($itemID7) && isset($itemID8) && isset($itemID9) && isset($itemID10)){    
        $upd = "UPDATE booking SET status='$status/ADM', verification='No'";
        if($CalDate !=''){
            $upd.= ",CalDate='$CalDate'";
        }
        if($DueDate !=''){
            $upd.= ",DueDate='$DueDate'";
        }
        if($groupid1 != 0){
            $upd.= " WHERE groupid IN ('$groupid1')";
        }
        if($groupid2 != 0){
            $upd.= " WHERE groupid IN ('$groupid2')";
        }
        if($groupid1 == 0){
            $upd.= " WHERE itemid IN ('$itemID1')";
        }
        if($groupid2 == 0){
            $upd.= " WHERE itemid IN ('$itemID2')";
        }
    }

Known errors: Error could not update data reason: 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 'WHERE itemid IN ('143')' at line 1

Either that or the script will only update the first line that it passes by. As for groupid1 != 0

And.. what is going wrong? What are the errors? You do know you can't have the WHERE multiple times in 1 query right? You should add them with AND — putvande
– putvande, Commented Jun 24, 2015 at 12:57
@putvande Error could not update data reason: 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 'WHERE itemid IN ('143')' at line 1 — Snoken
– Snoken, Commented Jun 24, 2015 at 12:57
maybe you have to concatenate this strings insted of setting them ? i dont know php but in this case you have to have this one $upd. += ",DueDate='$DueDate'" insted of $upd.= ",DueDate='$DueDate'"; — The Reason
– The Reason, Commented Jun 24, 2015 at 12:59
Either the error or only one of the updates will get through. I've updated the text @Reeno — Snoken
– Snoken, Commented Jun 24, 2015 at 13:00

Martin · Accepted Answer · 2015-06-24 13:01:17Z

1

The error is likely to be where you build your WHERE clauses.

It is probably due to the fact that you are adding multiple WHERE clauses into your statement. Your SQL statement can only contain one WHERE clause.

For example, if groupid1 and groupid2 are not 0 then you will get this statement:

SELECT ... WHERE groupid IN ('$groupid1') WHERE groupID IN ('$groupid2')

This is obviously invalid. You need to instead add a single WHERE clause and then add parameters using AND or OR to separate them as appropriate:

SELECT ... WHERE groupid IN ('$groupid1') OR groupID IN ('$groupid2')

answered Jun 24, 2015 at 13:01

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

The Reason Over a year ago

In this case you dont have both Where clause because your IF looks like a switch @Snoken

Martin Over a year ago

@The you are incorrect. Both $groupid1 and $groupid2 will be either zero or non-zero, in which case at least 2 IF statements will be true and this will result in two WHERE clauses.

Snoken Over a year ago

The problem is that I'm sending 10 values via input to this php file. Then I want it to scan every value. For each value that contains of a groupID change the status of that group. At the same time I want the script to UPDATE all values without any groupID but for those only individually

Martin Over a year ago

@Snoken You may wish to raise this as a separate question. I believe that your query as to why you are receiving an error message has been answered successfully.

Snoken Over a year ago

Yeah! I will try it out tomorrow and hopefully some results!

Collectives™ on Stack Overflow

Using multiple UPDATE in MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related