[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • Last Modified:

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...
0
Chris Millard
Asked:
Chris Millard
  • 3
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
UPDATE dbo.ACTIONS
SET nonbilltime = timetaken, timetaken = 0, ActionCode = -1
WHERE timetaken > 0
0
 
Philip PinnellCommented:
UPDATE ACTIONS
SET nonbilltime = nonbilltime + timetaken, timetaken = 0, ActionCode = -1
WHERE timetaken > 0
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
andycrofts' answer is correct; I missed the 'add add them to' part.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Chris MillardAuthor Commented:
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)
0
 
Philip PinnellCommented:
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
0
 
Chris MillardAuthor Commented:
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)
0
 
Chris MillardAuthor Commented:
OK - got it. The query was right, but I needed to right click on the database and select new query!
0
 
Philip PinnellCommented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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