'Query works in SQL Server, but not in Oracle

The following SQL statement works in SQL Server, but not in Oracle:

UPDATE example
SET ID = (SELECT MIN(ID) FROM example) + rownum

Oracle complains:

SQL command not properly ended

Why is this code works perfectly fine in SQL Server, and not in Oracle? Please help.

sqlsql-serveroracle


Solution 1:[1]

You must be wrong, stating that this works in SQL Server, but not in Oracle. It must be vice versa, because Oracle does have a ROWNUM function, while SQL Server has not:

For SQL Server you need ROW_NUMBER to create the row numbers and UDATE FROM in order to be able to access that row number in the update:

UPDATE example
SET id = (SELECT MIN(id) FROM example) + rownum
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS rownum FROM example) e;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5e0c61f5f2bd599191b439a45bcb5866

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Thorsten Kettner

Related Questions