OraFAQ Forum: SQL & PL/SQL » Oracle Update to get data from other tables using join

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Update to get data from other tables using join

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Update to get data from other tables using join [message #685815]

Fri, 01 April 2022 02:52

benoitS
Messages: 1
Registered: April 2022

Junior Member

This query does not work (on the 'from' : syntax error, exepeted element 'where') but illustrate what I am trying to do :

UPDATE rec
SET rec.END_DATE = TO_DATE(add_months(rec.dis_real_date, -12* dis.retention_period))
FROM record rec
JOIN component cpt ON rec.rec_id = cpt.rec_id
JOIN klass kla ON rec.kla_id = kla.kla_id
LEFT JOIN disposition_schedule dis ON kla.dis_id = dis.dis_id
WHERE rec.label IN (
'201203876',
'199831956',
'201203699'
'198521677');

I tried this query too (error: "missing SELECT keyword") :

UPDATE
(
record
inner JOIN component cpt ON record.rec_id = cpt.rec_id
inner JOIN klass kla ON record.kla_id = kla.kla_id
LEFT JOIN disposition_schedule dis ON kla.dis_id = dis.dis_id
)
SET
record.END_DATE = TO_DATE(
add_months(record.dis_real_date, -12 * dis.retention_period)
)
WHERE
record.label IN (
'201203876',
'199831956',
'201203699'
);

Could you explain why the queries don't work ?

Report message to a moderator

Re: Oracle Update to get data from other tables using join [message #685816 is a reply to message #685815]

Fri, 01 April 2022 03:22

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Use SQL*Plus and copy and paste your session, the WHOLE session.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

This is not a valid SQL in Oracle.
Have a look at MERGE statement.

Report message to a moderator

Re: Oracle Update to get data from other tables using join [message #685819 is a reply to message #685815]

Fri, 01 April 2022 07:10

Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA

Senior Member

Looks like you come from SQL Server background. Oracle UPDATE syntax is different. But before we go there look at:

TO_DATE(add_months(rec.dis_real_date, -12* dis.retention_period))

add_months returns DATE data type, TO_DATE expects string, therefore add_months result will be implicitly converted to string using session NLS_DATE_FORMAT and then back to data, again, using session NLS_DATE_FORMAT. So if, for example, NLS_DATE_FORMAT=DD-MON-RR you will lose time portion. So get rid of TO_DATE, all you need is

add_months(rec.dis_real_date, -12* dis.retention_period)

Now back to syntax:

UPDATE record rec
  SET rec.END_DATE = (
                      SELECT  add_months(rec.dis_real_date, -12* dis.retention_period)
                        FROM       component cpt
                              JOIN
                                   klass kla
                                ON rec.kla_id = kla.kla_id
                              LEFT JOIN
                                  disposition_schedule dis
                                ON kla.dis_id = dis.dis_id
                        WHERE rec.rec_id = cpt.rec_id
                     )
 WHERE rec.label IN (
                     '201203876',
                     '199831956',
                     '201203699'
                     '198521677'
                    )
  AND rec.rec_id IN (
                     SELECT  cpt.rec_id
                       FROM  component cpt
                    );

Using MERGE would be more readable. Assuming rec_id is unique:

MERGE
  INTO record t
  USING (
         SELECT  rec.rec_id
                 add_months(rec.dis_real_date, -12* dis.retention_period) end_date
           FROM       record rec
                 JOIN
                      component cpt
                   ON rec.rec_id = cpt.rec_id
                 JOIN
                      klass kla
                   ON rec.kla_id = kla.kla_id
                 LEFT JOIN
                      disposition_schedule dis
                   ON kla.dis_id = dis.dis_id
           WHERE rec.label IN (
                               '201203876',
                               '199831956',
                               '201203699'
                               '198521677')
        ) s
  ON (
      t.rec_id = s.rec_id
     )
  WHEN MATCHED
    THEN
      SET t.end_date = s.end_date
/

And if rec_id isn't unique

MERGE
  INTO record t
  USING (
         SELECT  rec.rowid rid
                 add_months(rec.dis_real_date, -12* dis.retention_period) end_date
           FROM       record rec
                 JOIN
                      component cpt
                   ON rec.rec_id = cpt.rec_id
                 JOIN
                      klass kla
                   ON rec.kla_id = kla.kla_id
                 LEFT JOIN
                      disposition_schedule dis
                   ON kla.dis_id = dis.dis_id
           WHERE rec.label IN (
                               '201203876',
                               '199831956',
                               '201203699'
                               '198521677')
        ) s
  ON (
      t.rowid = s.rid
     )
  WHEN MATCHED
    THEN
      SET t.end_date = s.end_date
/

SY.

Report message to a moderator

Previous Topic:	Remove empty tags from XML
Next Topic:	Data not sorted

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 09:24:19 CDT 2023