UPDATE column using values in same TABLE

[fionashaw]

I am trying to fix an issue for a customer which requires us to update a column in a table with the value in the column concatenated (||) with the value in another row in the same table.

The table holds comments on data items, where the data is held in datasets. Each year a new dataset is created by copying the previous years dataset but there is a problem where some of the comments were not copied and new comments have been added and they need to be merged.

I used to be an oracle wiz but am very rusty after 8 years of project managing, my developer is on leave and the fix he put in place doesn't work, so am trying to fix it myself:

This sql runs but the result is all rows are updated and the comment_text is now blank.

update
mx_comments
set
comment_text =
(
select comment_text||a .comment_text
from
mx_comments a
where
identnr = a.identnr
and
table_id = a.table_id
and
field_id = a.field_id
and
data_set_id2 = '&&to_data_se t'
and
a.data_set_id2 = '&&from_data_se t_id')


Any help would be appreciated

[rski]

Think you should alias the outer table like that

update
mx_comments  b
set
b.comment_text = 
(
select b.comment_text||a.comment_text 
from
mx_comments a 
where
b.identnr = a.identnr 
and
b.table_id = a.table_id 
and
b.field_id = a.field_id 
and
b.data_set_id2 = '&&to_data_set' 
and
a.data_set_id2 = '&&from_data_set_id')

[fionashaw]

This produced a very odd result, adding additional rows

I think I might go back to basics and create a new row based on the two I want to merge and then update the original with that value rather than trying to be clever with a one hit wonder

[rski]

I don't think that UPDATE could add rows to table.
Are you sure that inner query you wrote procduces right output?