asked on

SQL query to remove value from one column and add it to another

With regards to the example below, all of these fields are in a table called dbo.ACTIONS

I need an SQL query that will take any values >0 from timetaken and add them to nonbilltime. At the same time, timetaken should then be set to 0, and ActionCode should be set to -1

Faultid     actionnumber     timetaken     nonbilltime     ActionCode
110284     1                          2.33               0                      -1
110284     2                          0.08               0.08                 -1
110284     3                          0                    0.08                  0

Open in new window

Sorry about the layout - can't seem to get it to line up properly...

Jim Horn

UPDATE dbo.ACTIONS
SET nonbilltime = timetaken, timetaken = 0, ActionCode = -1
WHERE timetaken > 0

ASKER CERTIFIED SOLUTION

Andrew Crofts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

andycrofts' answer is correct; I missed the 'add add them to' part.

Chris Millard

ASKER

Hmm, I've modified the query to:-

UPDATE dbo.ACTIONS
SET nonbilltime = nonbilltime + timetaken, timetaken = 0, ActionCode=-1
WHERE Faultid = 110284 and timetaken>0

Open in new window

as I only want this to affect that specific Faultid, but the query returns (0 row(s) affected)

Andrew Crofts

try

select * from ACTIONS
WHERE Faultid = 110284

and check timetaken

Then

select * from ACTIONS
WHERE Faultid = 110284
and timetaken>0

let us know what you get

Chris Millard

ASKER

OK - on the first query I get the outcome as per the table in my orignal post, then on the second query, I get the same results but without the top row (actionnumber 1)

Chris Millard

ASKER

OK - got it. The query was right, but I needed to right click on the database and select new query!

Andrew Crofts

Thanks