How to use cross apply string split result to update a table in sql?-CodePudding

I am trying to split a column('categories') of a Table 'movies_titles' which has string separated data values in it.

e.g:
ID title     categories
1  Movie A   Comedy, Drama, Romance
2  Movie B   Animation
3  Movie C   Documentary, Life changing

I want to split the comma delimited string and place each values in a separate rows and update the table

-- this query shows the splitted strings as I want it
SELECT *
FROM dbo.movies_titles
CROSS APPLY
string_split(categories, ',') 

O/P:
ID title     categories                   value
1  Movie A   Comedy, Drama, Romance       Comedy
1  Movie A   Comedy, Drama, Romance       Drama
1  Movie A   Comedy, Drama, Romance       Romance
2  Movie B   Animation                    Animation
3  Movie C   Documentary, Life changing   Documentary
3  Movie C   Documentary, Life changing   Life changing

I want to use UPDATE query to set the result obtained from value column. I just don't want to use SELECT query to view the result but permanently update the changes to the table. How do I achieve this in sql server?

CodePudding user response：

You can do something similar to your intention creating new rows, because the update statement won't create the additional rows made by the split.

There can be issues if the ID column is unique, like a primary key, and there is the need to keep the title associated with that column.

I've created two scenarios on DB Fiddle, showing how you can do this using only one table as the question instructed, but a better alternative would be to save this information on another table.

This code on DB Fiddle: link

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

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

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

CodePudding user response：

It actually is possible to insert or update at the same time. That is to say: we can update each row with a single category, then create new rows for the extra ones.

We can use MERGE for this. We can use the same table as source and target. We just need to split the source, then add a row-number partitioned per each original row. We then filter the ON clause to match only the first row.

WITH Source AS (
    SELECT
      m.ID,
      m.title,
      category = TRIM(cat.value),
      rn = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))
    FROM movies m
    CROSS APPLY STRING_SPLIT(m.categories, ',') cat
)
MERGE movies t
USING Source s
ON s.ID = t.ID AND s.rn = 1
WHEN MATCHED THEN
  UPDATE
  SET categories = s.category
WHEN NOT MATCHED THEN
  INSERT (ID, title, categories)
  VALUES (s.ID, s.title, s.category)
;

db<>fiddle

I wouldn't necessarily recommend this as a general solution though, because it appears you actually have other normalization problems to sort out first. You should really have separate tables for all this information:

Movie
Category
MovieCategory