By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Saving time by using MySQL query command and the WHERE function

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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =26 LIMIT 1 ;
  2. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =27 LIMIT 1 ;
  3. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =28 LIMIT 1 ;
  4. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =29 LIMIT 1 ;
  5. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =30 LIMIT 1 ;
  6. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` =100 LIMIT 1 ;
Is there a command where I can just type one line such as


Expand|Select|Wrap|Line Numbers
  1. 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
Jul 15 '14 #1
Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,347
Expand|Select|Wrap|Line Numbers
  1. `id` BETWEEN 26 AND 100
Jul 15 '14 #2

P: 7
Thanks Rabbit! That worked.
Jul 15 '14 #3

P: 7
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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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!
Jul 15 '14 #4

Rabbit
Expert Mod 10K+
P: 12,347
Expand|Select|Wrap|Line Numbers
  1. CASE `price`
  2.    WHEN 7.99 THEN 3.99
  3.    WHEN 14.99 THEN 6.99
  4.    ELSE `price`
  5. END CASE
Jul 15 '14 #5

P: 7
So would it look like this

Expand|Select|Wrap|Line Numbers
  1. UPDATE `mytablename` SET `price` = '3.99' WHERE `id` BETWEEN 1 AND 100 
  2.  
  3.     CASE `price`
  4.        WHEN 7.99 THEN 3.99
  5.        WHEN 14.99 THEN 6.99
  6.        ELSE `price`
  7.     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
Jul 15 '14 #6

Rabbit
Expert Mod 10K+
P: 12,347
Expand|Select|Wrap|Line Numbers
  1. SET `price` = CASE ...
You already set the range on id with the BETWEEN.
Jul 16 '14 #7

P: 7
I am getting syntax errors with the following

Expand|Select|Wrap|Line Numbers
  1. UPDATE `mytablename` SET `price` = 
  2.  
  3. CASE `price`
  4. WHEN 7.99 THEN 3.99
  5. WHEN 14.99 THEN 6.99
  6. ELSE `price`
  7. 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

Expand|Select|Wrap|Line Numbers
  1. UPDATE `mytablename` SET `price` = 
  2.  
  3. CASE `price` WHERE `id` BETWEEN 1 AND 100
  4. WHEN 7.99 THEN 3.99
  5. WHEN 14.99 THEN 6.99
  6. ELSE `price`
  7. 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...
Jul 16 '14 #8

Rabbit
Expert Mod 10K+
P: 12,347
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/
Jul 16 '14 #9

Rabbit
Expert Mod 10K+
P: 12,347
It looks like in MySQL, the end of the CASE statement is just END and not END CASE.
Jul 16 '14 #10

P: 7
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.     UPDATE `mytablename` SET `price` = 
  3.  
  4.     CASE `price` 
  5.     WHEN 7.99 THEN 3.99
  6.     WHEN 14.99 THEN 6.99
  7.     ELSE `price`
  8.     END 
  9.  
  10.  
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

Expand|Select|Wrap|Line Numbers
  1.  UPDATE "mytablename` SET `price` = '3.99' WHERE `id` BETWEEN 3000 AND 5000 
Do you have any other references on using the BETWEEN with CASE
Jul 16 '14 #11

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

Expand|Select|Wrap|Line Numbers
  1. UPDATE `mytable` SET `price` =    
  2.  (
  3.           CASE `price`
  4.        WHEN 3.99 THEN 1.99
  5.        WHEN 9.99 THEN 6.99
  6.        ELSE `price`
  7.     END 
  8.   )
  9. WHERE `id` BETWEEN 2000 AND 4000 
Thanks again :)
Jul 17 '14 #12

Rabbit
Expert Mod 10K+
P: 12,347
Glad you were able to work it out. Good luck on the rest of your project.
Jul 17 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.