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