One MYSQL Query to Update Multiple Rows

[koopkoop]

I have a table like the one below. I have another table with prices in it that I would like to update my first table's prices with. I also need to be able to select which itemnumbers get their prices updated. I can handle the assembly of the query in PHP, but I have no idea what the actual MYSQL query would look like. What MYSQL query would I need to accomplish this?

 

Itemnumber| Price

----------------------

PL1222 | $3.40

AX1222 | $4.45

YX1222 | $2.31

[koopkoop]

^^^ Forgot to restate, but I want to do the entire update in one query.

[milesap]

$query[] = "UPDATE YourTable SET price='whatever' WHERE id='whatever'");

$query[] = "UPDATE YourTableTwo SET price='whatever' WHERE id='whatever'");

 

foreach($query as $id) {

    mysql_query($query[$id]);

}

 

[koopkoop]

^ Thanks, but that solution is using one query for every update. If I used your example to update 30 records, I would have used 30 queries. I'm looking for a query statement that will allow me to update 30 records with one query.

[milesap]

Ultimately, you need to create one query to update a table. However, you could put your queries in a loop if say you were updating the same table. If you are updating multiple tables with various variables then unfortunately I cannot think of a a quicker solution.

[koopkoop]

I know there's a way though because I found something close to what I'm trying to accomplish:

 

UPDATE test

    SET test_order = case test_id

          when 1 then 2

          when 2 then 3

          when 3 then 1 end

WHERE test_id in(1,2,3)

 

Hopefully, this helps clear things up more than confusing things further.

[printf]

The only way to do it with one query is to use a CASE statement, but explaining that would take forever. If you showed some real world example data I could give you an example of updating multiple rows with different values in a single query.

[koopkoop]

^ OK. Look at my original post. How would I update prices to a new value on only PL1222 and YX1222 with ONE query.

[printf]

what values...

 

update PL1222 with what value? (tell me the update value)

 

update YX1222 with what value? (tell me the update value)

[koopkoop]

Update only PL1222 to $5.00 and YX1222 to $6.00, in one query.

[redarrow]

ref      num

PL1222 | $3.40

AX1222 | $4.45

YX1222 | $2.31

 

$sql="UPDATE what_ever SET ref='New_num1234',ref='New_num5678' where num='$3.40' AND num='$2.31'";

 

my stab in the dark.

[koopkoop]

ref       num

PL1222 | $3.40

AX1222 | $4.45

YX1222 | $2.31

 

$sql="UPDATE what_ever SET ref='New_num1234',ref='New_num5678' where num='$3.40' AND num='$2.31'";

 

my stab in the dark.

 

Sorry, I don't think that'll work.

[koopkoop]

How would I update the price of itemnumbers PL1222 to $5.00 and YX1222 to $6.00, in one query?

 

Table:Products

 

Itemnumber| Price

----------------------

PL1222 | $3.40

AX1222 | $4.45

YX1222 | $2.31

[Philip]

If you were updating the values to the same, it would be possible, using OR in the WHERE clause. However, since you're using multiple update values, I am 95% sure that you can't update multiple rows with different update values without using a CASE.

[koopkoop]

How would I update the price of itemnumbers PL1222 to $5.00 and YX1222 to $6.00, in one query?

 

Table:Products

 

Itemnumber| Price

----------------------

PL1222 | $3.40

AX1222 | $4.45

YX1222 | $2.31

 

UPDATE products

SET CASE

WHEN itemnumber = PL1222 THEN price='$5.00'

WHEN itemnumber = YX1222 THEN price='$6.00'

 

Would this work?? Can someone please correct?

 

 

 

 

[Philip]

I believe your code would work but you need to add the end in.

 

Here's another way... explained below:

UPDATE `products` SET `price`=
CASE 
    WHEN `itemnumber`='PL1222' 
        THEN '$5.00'
    WHEN `itemnumber`='YX1222'
        THEN '$6.00'
    ELSE
        THEN `price`
END
WHERE 
    `itemnumber`IS NOT NULL

 

Your code will just update the ones you need update (a select few.) Lets say you want to raise the price on everything, change ELSE THEN `price` to something like ELSE THEN `price`+1 (however, you'd have to & I strongly recommend changing your datatype and have PHP or whatever output add the $ on the numbers)*, or set it to a default value (like $2.00): ELSE THEN `price`='$2.00'

 

Untested, but I believe it is correct.

 

* - Changing the datatype from a varchar to something like decimal, would allow you to do math in your queries as well as in PHP. You can easily echo a dollar sign (like, echo '$'.$row['price']; ) In your case, I'd highly recommend looking into switching it

 

[edit: added some formatting to text for easier reading]

[koopkoop]

Thanks for the pointers on using the generic 'price' statement. Does that mean I can do something like below to increase the existing price value for that particular itemnumber? eg:

 

UPDATE `products` SET `price`=

CASE

    WHEN `itemnumber`='PL1222'

        THEN 'price' + 3.00

    WHEN `itemnumber`='YX1222'

        THEN 'price' * 4

    ELSE

        THEN `price`

END

WHERE

    `itemnumber`IS NOT NULL

 

Yeah, I under stand the concern over the dollar signs. My actual db doesn't have them.

[Philip]

Yes you can. Now, I will warn you, with the code I posted and the code you posted above, it will run for every row in the table (if it does not match the case's, it will just set the price to what it was)

 

UPDATE `products` SET `price`=
CASE 
    WHEN `itemnumber`='PL1222' 
        THEN `price` + 3.00
    WHEN `itemnumber`='YX1222'
        THEN `price` * 4
    ELSE
        THEN `price`
END
WHERE 
    `itemnumber`IS NOT NULL

 

(remember backticks ` instead of single quotes ' on col names)

[koopkoop]

Is there another way of writing a query that would only affect the itemnumbers of interest?

 

 

[Philip]

I'll try to think of one, I'm thinking in the WHERE clause to use IN...

UPDATE `products` SET `price`=
CASE 
    WHEN `itemnumber`='PL1222' 
        THEN `price` + 3.00
    WHEN `itemnumber`='YX1222'
        THEN `price` * 4
END
WHERE 
    `itemnumber` IN ('PL1222' , 'YX1222')

Try that, I'm pretty sure that's correct.