how to modify a value in a comumn in a table SQL

Question

I have a table MYTABLE in a database with several columns.

a given column DOGCOLORS is valued in all rows with multiple values:

1: yellowredgreen 2: redpurplegreen 3: blackgreengray

I am trying to find a query that updates the value "green" with vaue "orange" in every value of the column DOGCOLORS in table MYTABLE

BEGIN TRANSACTION;
UPDATE MYTABLE
  SET DOGCOLORS=REPLACE(DOGCOLORS,'red','orange');
COMMIT TRANSACTION;

the result should be something like:

1: yellowredorange 2: redpurpleorange 3: blackorangegray

the query im running on DBeaver gives those errors: PL/SQL: ORA-02185: a token other than WORK follows COMMIT Encountered the symbol "end-of-file" when expecting one of the following:

;

any help appreciated

Answer 1

Just replace COMMIT TRANSACTION; with COMMIT;. Also you don't need 'BEGIN TRANSACTION;' it starts automatically

Answer 2

To update every substring "green" with vaue "orange" in plain SQL use this query

update MYTABLE
set dogcolors = replace(dogcolors,'green','orange')
where dogcolors like '%green%'
;
commit;

To call an anonymous PL/SQL block use

BEGIN
update MYTABLE
set dogcolors = replace(dogcolors,'green','orange2')
where dogcolors like '%green%'
;
commit;
END;
/

Note a good practice is to limit the updated row and exclude the rows that would be updated to the same value - see the WHERE predicate in the UPDATE statement.

To commit transaction you typically use only commit;, but if you need it a bit more verbose this is also a legal syntax (though used very rarely):

 commit work comment 'transaction';