CODEWITHSUNDEEP
sqlplsqlsubquerysql-update
MattB
MattB

Reputation: 724

Can't get "update from subquery" statement to work in Oracle SQL

I've looked all over, but can't figure out how to update a column in a table based on subquery data when matching on a customer ID. Here's some syntax to give an idea of what I'm trying to do:

UPDATE TableName
   SET TableName.Revenue = Z.Revenue
FROM
(
   SELECT
      CustomerID,
      sum(Revenue) as Revenue
   FROM
   (
      SELECT
         CustomerID,
         Revenue
      FROM
         TableA
      WHERE
         CustomerID in TableF
      UNION ALL
         SELECT
            CustomerID,
            Revenue
         FROM
            TableB
         WHERE
            CustomerID in TableF
   )
   GROUP BY
      CustomerID
) Z
WHERE
   TableName.CustomerID = Z.CustomerID

In essence, I'm looking to update a table column if another ID column under the same table matches an ID from a subquery. My goal is to avoid creating a whole new table from the subquery. Any help would be appreciated. Thanks.

Upvotes: 2

Views: 3302

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

Oracle does not support a FROM clause for an UPDATE. This will work:

UPDATE TableName
   SET TableName.Revenue =
(
   SELECT
      sum(Revenue) as Revenue
   FROM
   (
      SELECT
         CustomerID,
         Revenue
      FROM
         TableA
      WHERE
         CustomerID in (select CustomerID from TableF)
      UNION ALL
         SELECT
            CustomerID,
            Revenue
         FROM
            TableB
         WHERE
            CustomerID in (select CustomerID from TableF)
   )
   WHERE
      CustomerID = TableName.CustomerID
   GROUP BY
      CustomerID
);

Another option is to use the dml_expression_table syntax, which basically looks something like update (select a.x, b.y from a join b on a.a = b.b) set x = y. But that's kinda weird and requires unique constraints to work.

Or you could use MERGE with only an UPDATE section. It's unusual to use a MERGE for only an UPDATE, but since Oracle supports the ANSI standard it may help you use familiar syntax.

Upvotes: 3

Tom H
Tom H

Reputation: 47402

It looks like you're breaking normalization here (repeating the same data more than once in your database), which is likely to cause a LOT of problems down the road with trying to keep this column properly updated. Assuming that you understand that and still want to proceed, this should work:

UPDATE
    Table_Name
SET
    revenue = SUM(Z.revenue)
FROM
    Table_Name
INNER JOIN
(
    SELECT
        customer_id,
        SUM(revenue) AS revenue
    FROM
    (
        SELECT
            customer_id,
            revenue
        FROM
            Table_A
        WHERE
            customer_id IN (SELECT customer_id FROM Table_F)
        UNION ALL
        SELECT
            customer_id,
            revenue
        FROM
            Table_B
        WHERE
            customer_id IN (SELECT customer_id FROM Table_F)
    )
) Z ON
    Z.customer_id = Table_Name.customer_id

I think the problem may be that you don't have the main table in your FROM clause or specifically JOINed into the query. I don't do a lot of Oracle work, so I'm not positive, but that would be a problem if you ran this with MS SQL Server.

Upvotes: 2

Related Questions