UPDATE the result of derived table

Question

Just wanna ask, is it possible to update the result of a derived table.

My code is below:

UPDATE FROM (select a.*, row_number() over
 (partition by device_util_id 
  order by service_point_util_id, install_date desc) as rownum   
from eadwstage.test_device_locations a) tst
set DELTA_FLAG = 'D'
where tst.rownum <> 1

My query is throwing invalid SQL statement.

I also tried adding the DeviceID in WHERE condition but it updated both IDs into 'D'

Sample data are:

+----------+-------------+---------------+------------+
| DeviceID |    Location |    DELTA_FLAG |     ROWNUM |
+----------+-------------+---------------+------------+
|  1       |          US |              I|          1 |
|  1       |          UK |              U|          2 |
|  2       |          MY |              I|          1 |
|  3       |          JP |              I|          1 |
+----------+-------------+---------------+------------+

Basically, I wanted to update the records that have rownum > 1 to 'D' Delta_Flag.

Below is how I wanted:

+----------+-------------+---------------+------------+
| DeviceID |    Location |    DELTA_FLAG |     ROWNUM |
+----------+-------------+---------------+------------+
|  1       |          US |              I|          1 |
|  1       |          UK |              D|          2 |
|  2       |          MY |              I|          1 |
|  3       |          JP |              I|          1 |
+----------+-------------+---------------+------------+

*Note: the ROWNUM column is not part of the table

Databse is either Oracle or Teradata

In such cases, I would use a CTE to check whether the query fetches the correct rows which should be updated. If you succeeded to get those rows, you can update them. This can for example be done by simply joining the CTE to your table or even update the CTE itself. There are lots of articles/questions about this, an example here: https://stackoverflow.com/questions/60255248/update-records-in-table-from-cte-results — Jonas Metzler, Jun 30 '23 at 18:00
@JonasMetzler that is sql server specific, neither oracle or teradata supports it this would be a much better lin https://stackoverflow.com/questions/7363885/update-with-row-number-in-oracle-with-multiple-rows but as we don't know nothing about the original data it ias hard to tell — nbk, Jun 30 '23 at 18:17
Teradata does support `UPDATE tablename-or-ALIAS FROM list-of-inner-joined-tables-or-subqueries` — Fred, Jul 01 '23 at 17:35

score 1 · Accepted Answer · answered Jun 30 '23 at 20:17

UPDATE FROM is not valid Oracle SQL syntax.

In Oracle, you can use a correlated sub-query (correlating on the ROWID pseudo-column):

UPDATE eadwstage.test_device_locations
SET DELTA_FLAG = 'D'
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (
             PARTITION BY device_util_id 
             ORDER BY service_point_util_id, install_date DESC
           ) AS rn
    FROM   eadwstage.test_device_locations
  )
  WHERE  rn > 1
);

or a MERGE statement:

MERGE INTO eadwstage.test_device_locations dst
USING (
  SELECT ROW_NUMBER() OVER (
           PARTITION BY device_util_id 
           ORDER BY service_point_util_id, install_date DESC
         ) AS rn
  FROM   eadwstage.test_device_locations
) src
ON (src.ROWID = dst.ROWID AND src.rn > 1)
WHEN MATCHED THEN
  UPDATE
  SET delta_flag = 'D';

Thank you so much! this solved my problem – Xotigu Jul 01 '23 at 06:13 — Xotigu, Jul 01 '23 at 06:13

UPDATE the result of derived table

1 Answers1