CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3607525
user3607525

Reputation: 23

Change table data

Hopefully this will be easy for this community. I am a bit of a beginner. I am working on MS SQL server 2008 I need to transform some table data in a stored procedure.

SELECT Qty, WidgetNum, COLOR FROM wherever

I have table data like the following:

Qty | widgetnum       | COLOR
-----------------------------
2   | 100widget-TWO   | WHITE
1   | 200widget-THREE | BLACK

I need to transform it into the following table data:

Qty | widgetnum | COLOR
-----------------------------
4   | 100widget | WHITE
3   | 200widget | BLACK

I probably have 100 or so widget numbers to do this too and thousands of records in the table. Thanks

Upvotes: 0

Views: 59

Answers (1)

Dimitris Kalaitzis
Dimitris Kalaitzis

Reputation: 1426

If i understand your problem correctly this should do the trick: 

UPDATE wherever
SET 
Qty=Qty*( CASE
            WHEN widgetnum like '%-TW' THEN 2
            WHEN widgetnum like '%-TWO' THEN 2
            WHEN widgetnum like '%-THREE' THEN 3
            WHEN widgetnum like '%-THRE' THEN 3
            ELSE 1 END),
widgetnum=(CASE 
             WHEN widgetnum like '%-TW' THEN REPLACE(widgetnum,'-TW','')
             WHEN widgetnum like '%-TWO' THEN REPLACE(widgetnum,'-TWO','')
             WHEN widgetnum like '%-THREE' THEN REPLACE(widgetnum,'-THREE','')
             WHEN widgetnum like '%-THRE' THEN REPLACE(widgetnum,'-THRE','')
             ELSE widgetnum END)

Upvotes: 2

Related Questions