Jump to content

One MYSQL Query to Update Multiple Rows


koopkoop

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

 

 

 

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.