Strange Oracle behavior

Question

I'm trying to generate a uuid for each row in an existing table and set the same value for two different columns.

I wrote an update statement that I thought should do it, but it is setting a different uuid for each column. Why?

CREATE TABLE tbl (
    n number,
    r1 raw(32),
    r2 raw(32)
);

insert into tbl (n) values (1);
insert into tbl (n) values (2);
insert into tbl (n) values (3);

update (select r1, r2, sys_guid() as uuid FROM tbl) 
    set r1 = uuid, r2 = uuid;
    
select n, rawtohex(r1), rawtohex(r2) from tbl;

N	RAWTOHEX(R1)	RAWTOHEX(R2)
1	F89B6F66D7C7A52FE050020A02583951	F89B6F66D7C8A52FE050020A02583951
2	F89B6F66D7C9A52FE050020A02583951	F89B6F66D7CAA52FE050020A02583951
3	F89B6F66D7CBA52FE050020A02583951	F89B6F66D7CCA52FE050020A02583951

Answer 1

This is the same issue as this question and this question as the SYS_GUID function call is not being materialized in the sub-query and is being pushed to the update statement and called twice each row (once for each column); however, the answers to those questions don't quite work with an UPDATE statement.

Instead, you can use a MERGE statement:

MERGE INTO tbl d
USING (
  SELECT ROWID AS rid,
         SYS_GUID() AS uuid
  FROM   tbl s
) s
ON (s.rid = d.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET d.r1 = uuid,
      d.r2 = uuid;

Which, after the MERGE, your sample data may contain:

N	RAWTOHEX(R1)	RAWTOHEX(R2)
1	F89CAC01141C1982E053182BA8C018B2	F89CAC01141C1982E053182BA8C018B2
2	F89CAC01141D1982E053182BA8C018B2	F89CAC01141D1982E053182BA8C018B2
3	F89CAC01141E1982E053182BA8C018B2	F89CAC01141E1982E053182BA8C018B2

---

An alternative is to use two UPDATE statements:

UPDATE tbl
SET r1 = SYS_GUID();

UPDATE tbl
SET r2 = r1;

fiddle