-
Oracle UPDATE Joins
MS ACCESS and I think SQL Server makes this very easy for me, but Oracle does not. I'm looking to merge two tables that have identical keys. Essentially, I want to expand column-wise, not row-wise.
This is how it does work in Oracle
UPDATE MAIN m
SET col1 =
(
SELECT s.col1
FROM SIBLING s
WHERE m.key1 = s.key1
AND m.key2 = s.key2
AND m.key3 = s.key3
),
col2 =
(
SELECT s.col2
FROM SIBLING s
WHERE m.key1 = s.key1
AND m.key2 = s.key2
AND m.key3 = s.key3
);
But, I think it's silly that I have to code the query twice to do this. Perhaps processing-wise, Oracle will be efficient about it, and only run it once.
In MSFT, I get to do a join of the two tables, and then an update of one table, all in one shot.
Is there a way to streamline this in Oracle any better than I have?
Thanks
-
Yes Oracle does not allow FROM clause in an UPDATE statement however it allows to have multiple columns in set clause as
UPDATE MAIN m
SET (col1,col2) =
(
SELECT s.col1,s.col2
FROM SIBLING s
WHERE m.key1 = s.key1
AND m.key2 = s.key2
AND m.key3 = s.key3
);
-
That's great, just what I was looking for!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|