Background
Historically, updating a table based on values from another table in oracle database required developers to rely on correlated subqueries and or merge statements or other workarounds. Oracle ai database 26ai allows tables to be joined directly using an update and/or a delete statement. The result is cleaner SQL, more intuitive data modification queries, and a clearer expression of relationships between tables when performing updates.
With direct joins for update and delete statements, you can set values in one table using columns from another by
- Placing the source table in the from (or using) clause
- joining it to the target table in the where clause
The basic syntax for this is
update <target>
set <target>.<column> = <source>.<column>
from <source>
where <target>.<join_col> = <source>.<join_col>;Consider the below example on using this feature:
USAGE
Assume an online multiplayer game stores players and their current league(Bronze, Silver, Gold). A separate analytics process calculates a “new skill rating” for players after recent matches.
league rules
- Bronze: < 1000 skill score
- Silver: 1000-1499 skill score
- Gold: >= 1500 skill score
The dataset can be created As follows:
create table players
(
player_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
,gamer_tag VARCHAR2(32 CHAR)
,league VARCHAR2(32 CHAR)
) ;
Table PLAYERS created.
Elapsed: 00:00:00.062
create table rating_updates
(
player_id NUMBER PRIMARY KEY
,skill_score NUMBER
,CONSTRAINT rating_updates_fk FOREIGN KEY (player_id) references players(player_id)
) ;
Table RATING_UPDATES created.
Elapsed: 00:00:00.026
SQL> describe players
Name Null? Type
--------- -------- -----------------
PLAYER_ID NOT NULL NUMBER
GAMER_TAG VARCHAR2(32 CHAR)
LEAGUE VARCHAR2(32 CHAR)
SQL> describe rating_updates
Name Null? Type
----------- -------- ------
PLAYER_ID NOT NULL NUMBER
SKILL_SCORE NUMBER let’s insert some sample data to be used for the test
SQL> INSERT INTO players (gamer_tag, league)
VALUES ('shadowfox', 'Bronze'),
('pixelknight', 'silver'),
('novastorm','gold')
3 rows inserted.
Elapsed: 00:00:00.029
SQL> INSERT INTO rating_updates
VALUES (1, 820)
,(2,1210)
,(3,1600)
3 rows inserted.
Elapsed: 00:00:00.027
commit;
SQL> SELECT * FROM PLAYERS
PLAYER_ID GAMER_TAG LEAGUE
--------- ------------- --------
1 shadowfox Bronze
2 pixelknight silver
3 novastorm gold
Elapsed: 00:00:00.012
3 rows selected.
SQL> SELECT * FROM RATING_UPDATES
PLAYER_ID SKILL_SCORE
--------- -----------
1 820
2 1210
3 1600
Elapsed: 00:00:00.015
3 rows selected. The goal is to update each player’s league based on the latest calculated rating.
Pre-26ai example
Prior to 23/26ai we would use a correlated subquery syntax like this:
SQL> UPDATE PLAYERS p
SET
LEAGUE = (
SELECT
CASE
WHEN SKILL_SCORE < 1000 THEN
'Bronze'
WHEN SKILL_SCORE < 1500 THEN
'Silver'
ELSE
'Gold'
END
FROM
RATING_UPDATES R
WHERE R.player_id = p.player_id
);
3 rows updated.
Elapsed: 00:00:00.008
or using a merge like the below:
SQL> MERGE INTO players p
USING rating_updates R
ON (p.player_id = R.player_id)
WHEN MATCHED THEN UPDATE SET
p.league = DECODE(
SIGN(SKILL_SCORE - 1000),
-1, 'Bronze',
DECODE(
SIGN(SKILL_SCORE - 1500),
-1, 'Silver',
'Gold'
)
)
;
3 rows merged.
Elapsed: 00:00:00.012Oracle 26ai example
In oracle ai database 26ai the update can be written with a join directly in the statement like below:
SQL> UPDATE PLAYERS P
SET
LEAGUE =
CASE
WHEN R.SKILL_SCORE < 1000 THEN
'Bronze'
WHEN R.SKILL_SCORE < 1500 THEN
'Silver'
ELSE
'Gold'
END
FROM
RATING_UPDATES R
WHERE
P.PLAYER_ID = R.PLAYER_ID;
3 rows updated.
Elapsed: 00:00:00.007
Using the new syntax, the relationship between the tables is visible immediately and the logic for updating many rows from another dataset is much easier to write and follow.
conceptually it reads as “update each player by joining to their latest rating and assign the correct league”.
If the score for a player is updated in the player_ratings table the direct join update can be used to update the players table to set the player’s league properly.
The syntax can also be used for delete statements.
CONCLUSION
Direct join makes it easier to update tables using the results from another table rather than having to do a correlated subquery or merge. This can also improve code readability and reduce the amount of keystrokes which is always nice for developers.
Cheers 🎊
references:
https://oracleapex.com/ords/features/r/dbfeatures/features?feature_id=1697