# Oracle SQL Syntax Improvements

Oracle has been busy making a number of syntax changes in recent versions of Oracle. This blog aims to be a one stop shop for a few of the best ones (in my opinion).

### No More Dual

![](https://cdn.hashnode.com/uploads/covers/6a8b310b5f947d0eb679e4f5/d79fdc21-a169-48a1-b49a-521c320524fb.jpg align="center")

Ok, that's Duel rather than Dual (and only oldies will get it anyway). Moving on...

Since version 23c Oracle has made it no longer necessary to do this:

```sql
SELECT SYSDATE
FROM DUAL;
```

Instead you can just do this:

```sql
SELECT SYSDATE;
```

This can look a little bit strange till you get used to it but the above queries are equivalent. A couple of other examples without FROM DUAL:

```sql
SELECT 1
UNION ALL
SELECT 2;
```

```sql
SELECT LEVEL CONNECT BY ROWNUM <= 4;
```

### Direct Joins in Update and Delete Statements

You can now (v23ai/26ai) directly join tables within updates and deletes. These are more readable than using subqueries:

```sql
UPDATE EMP E
SET E.SAL = E.SAL + (E.SAL * .1)
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND D.LOC = 'YORKSHIRE';
```

Or this:

```sql
DELETE FROM EMP E
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND D.DNAME = 'RESEARCH';
```

### Insert Improvements

The SET clause - INSERT statements like UPDATE statements...

The following:

```sql
INSERT INTO CLAIMS (ID, CREATED_DT, CREATED_BY, RECEIVED_DT, STATUS)
VALUES
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :1, :2);
```

Can be replaced by:

```sql
INSERT INTO CLAIMS 
SET ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:1, STATUS=:2;
```

The SET clause is much easier for column to value alignment making them more readable and far less likely to set any columns to the wrong value.

Oracle also supports multirow inserts like these:

```sql
INSERT INTO CLAIMS (ID, CREATED_DT, CREATED_BY, RECEIVED_DT, STATUS)
VALUES
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :1, :2),
(CLAIM_SEQ.NEXTVAL, SYSDATE, USER, :3, :4);
```

And (with SET):

```sql
INSERT INTO CLAIMS 
SET (ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:1, STATUS=:2), (ID = CLAIM_SEQ.NEXTVAL, CREATED_DT=SYDATE, CREATED_BY=USER RECEIVED_DT=:3, STATUS=:4);
```

Note the use of parenthesis here to divide the rows.

I was curious to see if it was possible to use the returning clause returning into a collection e.g. RETURNING ID BULK COLLECT INTO r\_ids; but this was not possible for either of the above inserts and resulting in the following errors respectively:

> ORA-63853: RETURNING clause is not allowed with a multi-row INSERT INTO SET clause.

> ORA-63809: returning clause is not allowed with INSERT and Table Value Constructor

* * *

Follow my blog and [YouTube Channel](https://www.youtube.com/channel/UC_KKG03SmGysrr7eEDgJ2bQ) for more Oracle coding tips.