Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL Update table & Split one column into multiple rows

Please help!, I know how to spilt data from one column (seperated by ',')  and create new multiple rows, but how can I update the table at the same time?

Explaination:

I have a table with a few columns and in one of the columns (Col3) it has Emails seperated by a ',' like:

Col1   Col2              Col3  
----    -----               -----
1       ABC Ltd          pjones@ZZZ.com, wsmith@ABC.com
2       Compx Ltd      gbrown@xxx.com, Wills@xxx.com


I am using the following SQL code to split the emails and create new rows (with other columns retaining the same data):

Select *  FROM dbo.Table1  cross apply dbo.split(Col3,',' )

The SQL output is the following with new cols ID and Data that SQL inserts:

Col1             Col2              Col3                                                   Id              Data
----               -----               -----                                                     -----            -----
London       ABC Ltd          pjones@ZZZ.com, wsmith@ABC.com          1                pjones@ZZZ.com
London       Compx Ltd      gbrown@xxx.com, Wills@xxx.com              2               gbrown@xxx.com
London       ABC Ltd          pjones@ZZZ.com, wsmith@ABC.com          1                wsmith@ABC.com          
London       Compx Ltd      gbrown@xxx.com, Wills@xxx.com              2                Wills@xxx.com



But how do I Update Col3 with the new values from the Data column?, I have tried:

 update dbo.Table1
 set Col3 = F.Data, Col2 = Col2, Col1 = Col1
 Select *
 FROM dbo.Table1  cross apply dbo.split(Col,',' ) as F
 where f.id = f.id


Any help would be much appreciated!
0
Seven0fNine
Asked:
Seven0fNine
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
create a temp table first

select * into #tmp
from
(Select *  FROM dbo.Table1  cross apply dbo.split(Col3,',' )) x

then delete all

delete from myTable

insert into myTable select * from #tmp
0
 
HainKurtSr. System AnalystCommented:
(fix for above code) actually this one:

insert into myTable select * from #tmp
-->
insert into myTable
select Col1,Col2, Data from #tmp
0
 
HainKurtSr. System AnalystCommented:
you cannot use update since you are duplicating the number of records...
say you have 3 records, and after these operations you will have 5 records
so, you cannot achieve it just by an update statement...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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