SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- https://stackoverflow.com/questions/70331242/how-to-use-cross-apply-string-split-result-to-update-a-table-in-sql

--Assuming your table is something like this
create table movies_id_as_pk (
    ID int identity(1,1) primary key,
    title varchar(200),
    categories varchar(200),
    category varchar(200)
)
--Or this
create table movies_other_pk (
    another_id int identity(1,1) primary key,
    ID int,
    title varchar(200),
    categories varchar(200),
    category varchar(200)
)
--The example data
set identity_insert movies_id_as_pk on
insert into movies_id_as_pk (ID, title, categories) values
(1,  'Movie A',   'Comedy, Drama, Romance'),
(2,  'Movie B',   'Animation'),
(3,  'Movie C',   'Documentary, Life changing')
set identity_insert movies_id_as_pk off
insert into movies_other_pk (ID, title, categories)
    select ID, title, categories from movies_id_as_pk
6 rows affected

--You can't update directly any of the tables, because as the result of the split
--have more rows than the table, it would just leave the first value found:
update m set category = rtrim(ltrim(s.value))
from movies_id_as_pk m
cross apply string_split(m.categories, ',') as s

update m set category = rtrim(ltrim(s.value))
from movies_other_pk m
cross apply string_split(m.categories, ',') as s

select * from movies_id_as_pk
select * from movies_other_pk


ID
title
categories
category


1
Movie A
Comedy, Drama, Romance
Comedy

2
Movie B
Animation
Animation

3
Movie C
Documentary, Life changing
Documentary




another_id
ID
title
categories
category


1
1
Movie A
Comedy, Drama, Romance
Comedy

2
2
Movie B
Animation
Animation

3
3
Movie C
Documentary, Life changing
Documentary


--What you can do is create the aditional rows, inserting them:
--First, let's undo what the last instructions have changed
update movies_id_as_pk set category=NULL
update movies_other_pk set category=NULL

--Then use inserts to create the rows with the categories split
insert into movies_id_as_pk (title, category)
    select m.title, rtrim(ltrim(s.value))
    from movies_id_as_pk m
    cross apply string_split(m.categories, ',') as s

insert into movies_other_pk (ID, title, category)
    select m.ID, m.title, rtrim(ltrim(s.value))
    from movies_other_pk m
    cross apply string_split(m.categories, ',') as s

select * from movies_id_as_pk
select * from movies_other_pk


ID
title
categories
category


1
Movie A
Comedy, Drama, Romance
null

2
Movie B
Animation
null

3
Movie C
Documentary, Life changing
null

4
Movie A
null
Comedy

5
Movie A
null
Drama

6
Movie A
null
Romance

7
Movie B
null
Animation

8
Movie C
null
Documentary

9
Movie C
null
Life changing




another_id
ID
title
categories
category


1
1
Movie A
Comedy, Drama, Romance
null

2
2
Movie B
Animation
null

3
3
Movie C
Documentary, Life changing
null

4
1
Movie A
null
Comedy

5
1
Movie A
null
Drama

6
1
Movie A
null
Romance

7
2
Movie B
null
Animation

8
3
Movie C
null
Documentary

9
3
Movie C
null
Life changing

ID	title	categories	category
1	Movie A	Comedy, Drama, Romance	Comedy
2	Movie B	Animation	Animation
3	Movie C	Documentary, Life changing	Documentary

ID	title	categories	category
1	Movie A	Comedy, Drama, Romance	null
2	Movie B	Animation	null
3	Movie C	Documentary, Life changing	null
4	Movie A	null	Comedy
5	Movie A	null	Drama
6	Movie A	null	Romance
7	Movie B	null	Animation
8	Movie C	null	Documentary
9	Movie C	null	Life changing