Please help me with update :( - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Please help me with update :(

Author

Topic

Amanda1980
Starting Member

3 Posts

Posted - 2009-04-28 : 05:08:47

Hi,

I need to update a value in a table with the previos value+10 where two fields are used for reference. If there are no previos value then I only want to update Example:

Tablenamn: Table_X
Columns A, B, C

a b c
123 147 10
123 147 20
123 147 0
124 258 10
124 258 0
125 358 10
126 369 0
127 388 10

UPDATE Table_X
SET C=?
WHERE (B <> 0 AND C = 0)

I want to update every row where C=0 and B<>0 and A is the same for every row. If ther only is one instance of A then the value should be 10.

After the update the table should look like this:

a b c
123 147 10
123 147 20
123 147 30
124 258 10
124 258 20
125 358 10
126 369 10
127 388 10

Thankfull for every thougt on this!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 05:14:54

[code]
declare @Table_X table
(
a int,
b int,
c int
)
-- Sample Data for testing
insert into @Table_X
select 123, 147, 10 union all
select 123, 147, 20 union all
select 123, 147, 0 union all
select 124, 258, 10 union all
select 124, 258, 0 union all
select 125, 358, 10 union all
select 126, 369, 0 union all
select 127, 388, 10

-- Query
;with data (a, b, c, r)
as
(
select a, b, c, r = row_number() over (partition by a, b order by case when c <> 0 then 1 else 2 end, c)
from @Table_X
)
update d
set c = r * 10
from data d
where c = 0

select *
from @Table_X
order by a, b, c

/* RESULT

a b c
----------- ----------- -----------
123 147 10
123 147 20
123 147 30
124 258 10
124 258 20
125 358 10
126 369 10
127 388 10

(8 row(s) affected)

*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Amanda1980
Starting Member

3 Posts

Posted - 2009-04-28 : 05:26:10

Thanks! It looks a bit complex to me though. Can't figure out how I can apply this to my table when it has over one million rows and 30 columns.. Is it really applicable to me? Specially the select part:

insert into @Table_X
select 123, 147, 10 union all
select 123, 147, 20 union all
select 123, 147, 0 union all
select 124, 258, 10 union all
select 124, 258, 0 union all
select 125, 358, 10 union all
select 126, 369, 0 union all
select 127, 388, 10

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 05:50:26

you only need the query ;with data . . .

the INSERT INTO @Table_X is for me to create sample data for testing

KH
[spoiler]Time is always against us[/spoiler]

Amanda1980
Starting Member

3 Posts

Posted - 2009-04-28 : 08:05:12

Works like a charm, many thanks!! =D

quote:
Originally posted by khtan

you only need the query ;with data . . .

the INSERT INTO @Table_X is for me to create sample data for testing

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-28 : 08:37:42

quote:
Originally posted by Amanda1980

Works like a charm, many thanks!! =D

quote:
Originally posted by khtan

you only need the query ;with data . . .

the INSERT INTO @Table_X is for me to create sample data for testing

KH
[spoiler]Time is always against us[/spoiler]

But beware if the previous & next values are 0 you may have problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources