Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Update to get data from other tables using join
icon9.gif  Oracle Update to get data from other tables using join [message #685815] Fri, 01 April 2022 02:52 Go to next message
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 ?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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.

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 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: Remove empty tags from XML
Next Topic: Data not sorted
Goto Forum:
  


Current Time: Fri Mar 29 03:58:55 CDT 2024