Solved

# SQL - Multiplication Query and updating to the Column

Posted on 2011-09-07
Medium Priority
480 Views
SQL 2008 :-

In my Table :TableName,  i have 3 columns [Ship Qty], Size, Qty.

Default value for Qty  is NULL. I wrote an Update Query to update the column : Qty. I need to multiply [Ship Qty] * Size.

Column : Size is nvarchar. It carries two conditons

1) If the value is 60 EA or 3 ML, I need to consider only 60 or 3 or 3.5 ...
2) If the value is 60X3 ML, I need to consider 60X3 which is 180. Then, this multiplied value will be multiplied with [Ship Qty].

Ship Qty    Size             Qty
1           100 EA           100
3           60 EA            180
2           60X3ML           360
``````update [TableName] set Qty = [Ship Qty] * CONVERT(INT, (Size)) * CONVERT(Float, (Size))                 WHERE  Size > 0                     AND Size > 0                     AND Qty IS NULL
``````
0
Question by:chokka
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 5

Accepted Solution

DavidMorrison earned 2000 total points
ID: 36496908
Try

update TN
set TN.Qty =
(case TN.size
when '100 EA' then 100
when '60 EA' then 60
when '3 ML' then 3
when '60x3ML' then 180
end) * TN.[Ship Qty]
from TableName as TN
0

Author Closing Comment

ID: 36496937
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll