Updating using Coalesce - 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)

Updating using Coalesce

Author

Topic

Leppy43
Starting Member

3 Posts

Posted - 2012-04-10 : 12:43:37

My assumption is I need to use Coalesce in some way??

I have two tables. One needs to get updated with data from the other. Currently it uses DISTINCT but I want it to be more flexible to concatenate (with a delimiter) the data it's using to update.
The following will hopefully explain my issue using sample SQL code:

create table MainTable (aa varchar(10), bb varchar(20))
insert into MainTable(aa, bb) values ('a1', 'John')
insert into MainTable(aa, bb) values ('a1', 'John')
insert into MainTable(aa, bb) values ('b1', 'Paul')
insert into MainTable(aa, bb) values ('a1', 'Peter')

Now I have this in MainTable:
a1 John
a1 John
b1 Paul
a1 Peter

create table MinorTable (v1 varchar(10), v2 varchar(20))
insert into MinorTable(v1, v2) values ('a1', NULL)
insert into MinorTable(v1, v2) values ('b1', NULL)

Now I have this in MinorTable:
a1 NULL
b1 NULL

I want to update MinorTable to end up with this:
a1 John/Peter
b1 Paul

Notice if the first column in MainTable matches the first column in MinorTable, I want to concatenate the values in the second column of MainTable to populate the second column of MinorTable.

Any advice on how to do this on the fly using something like this:
update MinorTable set v2 = (select distinct bb from MainTable MAT where MAT.aa = MinorTable.v1)

This code doesn't work because it returns more than one value. This is the code that already exists that I'm trying to apply a change to.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:04:56

[code]
update mr
set mr.v2 = stuff(p.q,1,1,'')
from MinorTable mr
cross apply (select distinct '/' + bb
from MajorTable
where aa = mr.v1
order by '/' + bb
for xml path('')
)p(q)
[/code]

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

Leppy43
Starting Member

3 Posts

Posted - 2012-04-10 : 20:18:11

Thank you visakh16!! This appears to work so far and will save me a huge amount of effort.

Are there any downsides to using cross apply? Normally I will be processing a few thousand rows running this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources