Saving time by using MySQL query command and the WHERE function

[intrepid21]

I have a database with some products in it. I can update one of the
products with a SQL query by the command

UPDATE `mytablename` SET `price` = '7.99' WHERE `id` =26 LIMIT 1 ;

In order for me to update about 100 products to a price '3.99' I have to
go in excel and do some concatenations( linking) and then copy and paste a hundred
individual command lines of

UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =26 LIMIT 1 ;
UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =27 LIMIT 1 ;
UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =28 LIMIT 1 ;
UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =29 LIMIT 1 ;
UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =30 LIMIT 1 ;
UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =100 LIMIT 1 ;

Is there a command where I can just type one line such as

UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =26-100 LIMIT 1 ;

Do I need to use the WHERE Command?

I am new to MySQL but really want to learn it becaue manipulating it in
excel is taking a long time. Anything would help.

Thanks

[Rabbit]

`id` BETWEEN 26 AND 100

[intrepid21]

Thanks Rabbit! That worked.

[intrepid21]

Lets say I have 100 products with 'id' 1-100 that are mostly 7.99 (price) and every once in a while have a product for 14.99 in that 'id' range

I would like to change the price of the ones listed at 7.99 to 3.99 and the ones listed at 14.99 to 6.99 within that 'id' range of 1-100

I know I can use the UPDATE and BETWEEN function such as

UPDATE `mytablename` SET `price` = '3.99' WHERE `id` BETWEEN 1 AND 9

but if the 10th 'id' where 14.99 I would have to run another single command

UPDATE `mytablename` SET `price` = '6.99' WHERE `id` =10 LIMIT 1 ;

Is there a way to combine these into one single command so that it will look for all the 7.99 prices within that 'id' range and UPDATE them to 3.99 and look for all the 14.99 prices within that 'id' range and update them to 6.99

This would save me so much time!

[Rabbit]

CASE `price`
   WHEN 7.99 THEN 3.99
   WHEN 14.99 THEN 6.99
   ELSE `price`
END CASE

[intrepid21]

So would it look like this

UPDATE `mytablename` SET `price` = '3.99' WHERE `id` BETWEEN 1 AND 100 

    CASE `price`
       WHEN 7.99 THEN 3.99
       WHEN 14.99 THEN 6.99
       ELSE `price`
    END CASE

I'm just confused where to set the range for the 'id' because I have thousands of other products within the table that I don't want to update

[Rabbit]

SET `price` = CASE ...

You already set the range on id with the BETWEEN.

[intrepid21]

I am getting syntax errors with the following

UPDATE `mytablename` SET `price` = 

CASE `price`
WHEN 7.99 THEN 3.99
WHEN 14.99 THEN 6.99
ELSE `price`
END CASE

#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 'CASE' at line 7



Also when I try it like this

UPDATE `mytablename` SET `price` = 

CASE `price` WHERE `id` BETWEEN 1 AND 100
WHEN 7.99 THEN 3.99
WHEN 14.99 THEN 6.99
ELSE `price`
END CASE

#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 'WHERE `id` BETWEEN 1 AND 100
WHEN 7.99 THEN 3.99
WHEN 14.99 THEN 6.99
ELSE `p' at line 3

Any hint or solution would help...

[Rabbit]

Please use tags when posting code or formatted data.

In your first attempt, your error code says line 7 when it looks like it means line 3. Which means that the code you posted is not the code you tried to run.

In your second attempt, for some reason you put the WHERE condition inside the CASE when I didn't do that in my code.

It looks like you are having a lot of trouble with basic SQL. Before you take on a project like this, you should probably learn the basics first.

Here is a tutorial you can use to learn the basics: http://www.w3schools.com/sql/

[Rabbit]

It looks like in MySQL, the end of the CASE statement is just END and not END CASE.

[intrepid21]

Thanks Rabbit. I have read the tutorial and doesn't have much on using the CASE with the BETWEEN functions

The code below works great

    UPDATE `mytablename` SET `price` = 
     
    CASE `price` 
    WHEN 7.99 THEN 3.99
    WHEN 14.99 THEN 6.99
    ELSE `price`
    END

However this will update every ProductID with a price of 7.99 to 3.99 and every ProductId that is 14.99 to 6.99 which is great but I'm only trying to update a range of ProductIds from over 10,000

The BETWEEN function I used earlier would also only update a range of ProductID's with one value

 UPDATE "mytablename` SET `price` = '3.99' WHERE `id` BETWEEN 3000 AND 5000

Do you have any other references on using the BETWEEN with CASE

[intrepid21]

Rabbit Thanks for your help! I'm an novice to this but it worked out.

UPDATE `mytable` SET `price` =    
 (
          CASE `price`
       WHEN 3.99 THEN 1.99
       WHEN 9.99 THEN 6.99
       ELSE `price`
    END 
  )
WHERE `id` BETWEEN 2000 AND 4000

Thanks again :)

[Rabbit]

Glad you were able to work it out. Good luck on the rest of your project.