CODEWITHSUNDEEP
sqloracle-databaseselect
Uchenna F. Okoye
Uchenna F. Okoye

Reputation: 1036

SQL Missing Select statement - Update Oracle

I can't seem to figure out what's wrong with this SQL code for Oracle. I am trying to update the database based on a subquery. 

with T as 
(SELECT "Folder Name", SUM("Session Length") as "Folder Length" 
 FROM "SESSIONUSAGE" 
 GROUP BY "Folder Name")
UPDATE FolderUsage SET ("Folder Length") = 
(SELECT "Folder Length" FROM T
    WHERE (FolderUsage."Folder Name" = T."Folder Name")

I needed the spaces as that's how I found the database. Thanks for your help!

Upvotes: 1

Views: 829

Answers (2)

user5683823
user5683823

Reputation:

The proper syntax is

update FolderUsage
  set "Folder Length" =
    ( 
       with T as ( <aggregate SELECT statement defining T goes here> )
       select "Folder Length" from T where ........
    )

The WITH clause belongs with the SELECT statement of which it is a subquery; the WITH clause does NOT go above the UPDATE clause!

However, in this case you may do better with the MERGE statement:

merge into FolderUsage F
  using ( <aggregate SELECT statement defining T goes here> ) T
    on (F."Folder Name" = T."Folder Name")
when matched then update set "Folder Length" = T."Folder Length"

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

For one thing, your parentheses don't balance:

with T as (
      SELECT "Folder Name", SUM("Session Length") as "Folder Length" 
      FROM "SESSIONUSAGE" 
      GROUP BY "Folder Name"
    )
UPDATE FolderUsage
    SET "Folder Length" = (SELECT "Folder Length"
                           FROM T
                           WHERE FolderUsage."Folder Name" = T."Folder Name"
                          );

But, Oracle still won't accept this. So, instead:

UPDATE FolderUsage
    SET "Folder Length" = (SELECT SUM(su."Session Length")
                           FROM "SESSIONUSAGE" su
                           WHERE FolderUsage."Folder Name" = su."Folder Name"
                          );

I think this is simpler anyway.

Upvotes: 0

Related Questions