Updating multiple rows in same table

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

General SQL Server Forums

New to SQL Server Programming

Updating multiple rows in same table

Author

Topic

SqlLrnr19
Starting Member

3 Posts

Posted - 2012-08-31 : 20:34:25

Hello,

I need to copy/place the data from one column into another column for every row in the table, where the data from the first column corresponds to row ID. This way, for evey row in the table, there is corresponding data and no nulls in the second column.

Here is a graphical representation of what I need:

This is what I am starting with:

ticket_____________________event___________________event1
04-14-11-0000520______________NULL____________________NULL
04-14-11-0000520______________NULL____________________NULL
04-14-11-0000520______________BRF1104000130___________NULL
04-14-11-0000520______________NULL____________________NULL

What I need to do is this:

ticket_______________event___________________event1
04-14-11-0000520________NULL____________________BRF1104000130
04-14-11-0000520________NULL____________________BRF1104000130
04-14-11-0000520________BRF1104000130___________BRF1104000130
04-14-11-0000520________NULL____________________BRF1104000130

Note how the second table now has an event number for each ticket number, whereas before there was only one instance of an event number for the entire series of tickets.

I would appreciate the code (I'm thinking its an Update with a join or a case statement, I just cant seem to get it right, nor am I finding this anywhere on the web), but if you know of a link elsewhere on these forums that solves this problem, please let me know. Thank you all in advance!

~ D

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 20:57:51

[code]
update t
set event1 = event
from yourtable t
inner join
(
select ticket, event = max(event)
from yourtable
where event is not null
group by ticket
) e on t.ticket = e.ticket[/code]

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

SqlLrnr19
Starting Member

3 Posts

Posted - 2012-09-01 : 03:34:08

Khtan,

Thanks for the reply, but this doesnt seem to work.

First, I had to modify your query because as written it throws a 'ambiguous table name' error. By putting the aliases in front of the column names in the 'set' parameter, I was able to work around it:

update t
set t.event1 = t.[event]
from dbo.mytablename t
inner join
(
select ticket, [event] = max([event])
from dbo.mytablename
where [event] is not null
group by ticket
) e on t.ticket = e.ticket

I also had to put brackets around the word 'event' as that is a reserved SQL word.

This query runs, but all it does is copy over data from 'event' to 'event1' where there is data in the event column; all other rows remain 'null', like this:

ticket__________________event___________________event1
04-14-11-0000520________NULL____________________NULL
04-14-11-0000520________NULL____________________NULL
04-14-11-0000520________BRF1104000130___________BRF1104000130
04-14-11-0000520________NULL____________________NULL
05-17-12-9200527________NULL____________________NULL
05-17-12-9200527________BRF8194000668___________BRF8194000668
05-17-12-9200527________NULL____________________NULL

etc...

Further guidance/advice is welcome. Thanks again,

~ D

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-01 : 05:47:38

[code]
update t
set [event1] = e.[event]
from yourtable t
inner join
(
select ticket, [event] = max([event])
from yourtable
where [event] is not null
group by ticket
) e on t.ticket = e.ticket
[/code]

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

SqlLrnr19
Starting Member

3 Posts

Posted - 2012-09-01 : 17:09:15

That did it. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources