• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

MySQL Edit Syntax

I have a table called 'charges'

I want to update the value of field 'calculated_weight' based on the contents of 'department_id', which will determine what to multiply against 'quantity' to determine the update value for 'calculated_weight'.  Example:

UPDATE `charges` SET `calculated_weight` = IF(`department_id` IS 4120, 1, 2);

Now, that isn't want I want, but puts into logical form the paragraph above it *almost.  

What I really want is to insert (quantity*.0857) into `calculated_weight` when `department_id` == 4120.
IF
'department_id' == 4250, then i would want (quantity*1.0147) to insert into 'calculated_weight'

I have about 6 departmentIDs to monitor and adjust the mutlplication value for.  

I tried a few options in MySQL, but it isn't working out for me.  

Thanks
0
weklica
Asked:
weklica
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
GaryCommented:
Why not this for each department and repeat just adjusting the calculation

UPDATE `charges` SET `calculated_weight` = (quantity*.0857) where department_id=4120

edited
For multiple departments then just an OR

UPDATE `charges` SET `calculated_weight` = (quantity*.0857) where department_id=4120 OR department_id=4121 OR department_id=4122
0
 
AshokCommented:
Try

UPDATE `charges` SET
`calculated_weight` =
CASE `department_id` WHEN 4120 THEN quantity*.0857
WHEN `department_id` WHEN 4250 THEN quantity*1.0147
....
ELSE 'Charges' END
0
 
NorieVBA ExpertCommented:
Have you tried using CASE..WHEN?

UPDATE `charges`
SET `calculated_weight` =
  CASE
     WHEN `department_id`= 4120 THEN quantity*0.0857
     WHEN `deparment_id` = 4250 THEN quantity*1.0147
      -- etc
  END
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
weklicaAuthor Commented:
That works perfectly.  I thought I tried something like that, but clearly missed something.  I used this specifically and it worked:

UPDATE  `charges` SET  `calculated_weight` = (  `quantity` * .0857 ) WHERE  `department_id` =4120
0
 
weklicaAuthor Commented:
I just noticed the CASE when option and will ultimately go wtih that one.  Very much appreciated.  You guys are awesome.
0
 
GaryCommented:
I did a small edit for doing multiple departments.
0
 
NorieVBA ExpertCommented:
cathal

If you have multiple deparments why not use an IN clause.

`deparment_id` IN (4120, 4121, 4122)
0
 
AshokCommented:
I have about 6 departmentIDs to monitor and adjust the mutlplication value for.  

If you are going to update all 6 departments (the whole table), you do not need

where `deparment_id` IN (4120, 4121, 4122)
0
 
GaryCommented:
Cos I was having a dumb blonde moment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now