Reputation: 724
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
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
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