need a help for Update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need a help for Update statement

Author

Topic

CoolMoon2011
Starting Member

31 Posts

Posted - 2011-11-18 : 12:33:14

Hey all, I want to have a update statement to combine units together when wp=0 from below data,
May I write codes like this below or any better ideas? Thanks ahead here.

Explain more about for the same person A

update from table1
set units =sum(units)
when wp=0

person, units, WP
A, 10, 450,
A, 5, 0,

Expect results:

person, units, WP
A, 15 , 450 ,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:38:09

how do define the order for finding below record? please keep in mind that there's no concept of order in a table unless you specify it on basis of column value by means of ORDER BY. is there a column for you to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:43:28

ok...so you want to delete some records after merging them onto a single record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CoolMoon2011
Starting Member

31 Posts

Posted - 2011-11-18 : 12:44:23

Yes, this is just part of of my codes in my procedure. I just need a update statment to update the table about units when wp=0. because it will be splited into two different units for the same person.

I don't want to delete the person when wp=0, just combine them together

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:46:01

do you have unique valued column (may be pk) in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:49:23

quote:
Originally posted by CoolMoon2011

Yes, this is just part of of my codes in my procedure. I just need a update statment to update the table about units when wp=0. because it will be splited into two different units for the same person.

I don't want to delete the person when wp=0, just combine them together

Not all the person records i mean. but the ones with wp=0 should be deleted right after merging with other record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CoolMoon2011
Starting Member

31 Posts

Posted - 2011-11-18 : 12:50:45

I want to have update statemnt like below, but when I set units, could I set units =sum(units)

update from table1
set units =sum(units)
when wp=0

CoolMoon2011
Starting Member

31 Posts

Posted - 2011-11-18 : 12:53:21

Not all the person records i mean. but the ones with wp=0 should be deleted right after merging with other record?

I see what you mean. You are right, after merging with other records the person will be deleted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 13:03:47

[code]
update t
set t.units = t.units + zerounits
from
(
select units,sum(case when WP=0 then units else 0 end) over (partition by person) as zerounits
from table1
)t
where WP <> 0

delete from table1 where WP=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CoolMoon2011
Starting Member

31 Posts

Posted - 2011-11-18 : 14:19:08

what dose mean about over (partition by person)

sum(case when WP=0 then units else 0 end) over (partition by person

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 01:25:55

quote:
Originally posted by CoolMoon2011

what dose mean about over (partition by person)

sum(case when WP=0 then units else 0 end) over (partition by person

it groups the table on basis of person and finds sum of units where WP=0 within each group

see
http://msdn.microsoft.com/en-us/library/ms189461.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources