How to update a table with joining two other tables

Question

We have three tables here, Session table which is connected to Green_fact table with Session_id field. Time_session table is the third table which is made of SessionDate from Session table with a primary key for every single day.

all tables

How can I populate the Date_ID field in Green_fact table. the code below is what I think use, but it doesn't work properly

update green_fact 
   inner join "SESSION" on green_fact."SESSION_ID" = "SESSION"."SessionID"
   inner join "TIME_SESSION" on "TIME_SESSION"."SESSION_DATE" = "SESSION"."SessionDate"
   set green_fact."DATE_ID" = "TIME_SESSION"."ID" where green_fact."SESSION_ID" = "SESSION"."SessionID";

Possible duplicate of [Update statement with inner join on Oracle](http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) — Kris Johnston, Mar 31 '17 at 13:21
Just a side note: You shouldn't store data redundantly. But well, maybe you are going to remove `session_date` from `session` after the update. — Thorsten Kettner, Mar 31 '17 at 13:46

score 2 · Accepted Answer · answered Mar 31 '17 at 13:02

Oracle doesn't allow join in update. You can use correlated subqueries:

update green_fact gf 
   set DATE_ID = (select ts.ID
                  from SESSION s join
                       TIME_SESSION ts
                       on ts.SESSION_DATE = s.SessionDate
                  where gf.SESSION_ID = s.SESSIONID
                 )
   where exists (select ts.ID
                 from SESSION s join
                      TIME_SESSION ts
                      on ts.SESSION_DATE = s.SessionDate
                 where gf.SESSION_ID = s.SESSIONID
                );

The exists may not be necessary, if all the rows match.

Linnof can you help me with this one ? https://i.stack.imgur.com/B2GaZ.png it is the same but with one less table — andrew, Mar 31 '17 at 14:51

score 2 · Answer 2 · answered Mar 31 '17 at 13:13

In Oracle you can either update a table or an updatetable query, i.e. UPDATE tablename SET ... or UPDATE (SELECT ... FROM ...) SET ....

update 
(
  select gf.date_id, time_session.id as time_session_id
  from green_fact gf
  inner join session s on gf.session_id = s.sessionid 
  inner join time_session ts on ts.session_date = s.sessiondate 
)
set date_id = time_session_id;

This will work, provided the DBMS sees it guaranteed that the query produces one row only per green_fact record (which it should, because of the primary and foreign keys).

score 1 · Answer 3 · answered Mar 31 '17 at 13:00

1

Use below query for update from two tables :

UPDATE green_fact SET green_fact."DATE_ID" = A.Id
FROM
(
    SELECT "TIME_SESSION"."ID" Id ,  "SESSION"."SessionID" SessionID
    FROM "TIME_SESSION" 
    JOIN "SESSION" ON  "TIME_SESSION"."SESSION_DATE" =      
    "SESSION"."SessionDate"
) A
WHERE green_fact."SESSION_ID" = A.SessionID;

answered Mar 31 '17 at 13:00

Mansoor

4,061
1
17
27

thanks for your reply, why do I get this error ? "SQL command not properly ended" I think some thing is missing before "FROM". – andrew Mar 31 '17 at 13:05
@andrew,Check your query,any parenthesis or any alias name is missing..? – Mansoor Mar 31 '17 at 13:09
1

Are you sure this is valid in Oracle? I doubt that `UPDATE ... FROM` is allowed. Syntax here: https://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708 – Thorsten Kettner Mar 31 '17 at 13:16
`FROM` is not allowed in an UPDATE statement in Oracle – Mar 31 '17 at 13:36

score 0 · Answer 4 · answered Apr 01 '17 at 01:32

Oracle doesn't allow joins to be used in UPDATE statements, but sometimes one can rewrite such a statement as a MERGE (you don't specify the version of Oracle you're using, but since 10g one can omit the WHEN MATCHED or WHEN NOT MATCHED clauses of the MERGE):

MERGE INTO green_fact gf
USING (
    SELECT s."SessionID", ts.session_date, ts.id
      FROM session s INNER JOIN time_session ts
        ON s."SessionDate" = ts.session_date ) ts1
   ON ( gf.session_id = ts1."SessionID" )
 WHEN MATCHED THEN
   UPDATE
      SET gf.date_id = ts1.id;

Hope this helps.

By the way, I cannot stress enough that mixed-case object names in Oracle are a bad idea. But maybe you're dealing with legacy data and don't have a choice.

How to update a table with joining two other tables

4 Answers4