How do I merge data from multiple rows in one table to a single column in a new table?

create table new_paragraphs
(
    id NUMBER
    paragraph CLOB
);

create table old_paragraphs
(
   id
   paragraph CLOB
);

merge into new_paragraphs  a
using (select * from old_paragraphs) b
on (id = id)
when matched then
update set a.paragraph = a.paragraph || b.paragraph;
-- Results in error: unable to get a stable set of rows in the source tables

The above throws an exception.

It would work if id were a primary key in at least *old_paragraphs* (or if it were unique for each id found in *new_paragraph*)

Other than that, you want to use aliases in on (id = id) so that it reads on (a.id = b.id):

merge into new_paragraphs  a
using (select * from old_paragraphs) b
on (a.id = b.id)
when matched then
update set a.paragraph = a.paragraph || b.paragraph;

more