3 replies to this topic

[makamo66]

I am trying to do an UPDATE with the SET clause:

UPDATE temp_cart SET quantity IN (7, 6) WHERE product_id IN ( 2, 4 ) AND user_id = '" . $_SESSION['UID'] . "'

I know that SET is used only with an equal sign like this:

UPDATE temp_cart SET quantity = 6 WHERE product_id IN ( 2, 4 ) AND user_id = '" . $_SESSION['UID'] . "'

However, I need a way to set the quantity column to more than one value so I want to use the IN clause. I can't just run the query twice because in reality the query is using variables with imploded values and not simply the integers shown here. The query actually looks like this:

$id_nums = implode(", ", $productId2);
$qty_nums = implode(", ", $qtyBox2);
UPDATE temp_cart SET quantity IN ($qty_nums) WHERE product_id IN ($id_nums) AND user_id = '" . $_SESSION['UID'] . "'

I am using MySQL version 5.0. The error message I get is fairly non-descript. It is merely:

Error occured while inserting values into table: 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 (7, 6) WHERE product_id IN ( 2, 4 ) AND user_id = '20100604164333'' at line 1

[WingedPanther]

That's because each field in each record has to be set to a specific value. You can't ask the database to choose one from a list.

[makamo66]

If I do this:

$ret3 = $mysqli->query("UPDATE temp_cart SET quantity = '7,6' WHERE product_id IN (2,4) AND user_id = '" . $_SESSION['UID'] . "' ");

then the quantity is updated to 7 for both of the product ids.

[makamo66]

This one worked

$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN 2 THEN 7 "
. " WHEN 4 THEN 6 "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";

However when I tried to use variable names for the arrays it didn't work any more. I tried the following with and without a while loop and neither one worked:

$i=0;
while ($i<=count($qty_nums)){
$i++;
$sql = "UPDATE temp_cart SET quantity = "
. " ( CASE product_id "
. " WHEN ($id_nums) THEN ($qty_nums) "
. " END ) "
. " WHERE user_id = '" . $_SESSION['UID'] . "' ";

$ret3 = $mysqli->query($sql);
}

The error message was this:

Error occured while inserting values into table: Operand should contain 1 column(s)