CODEWITHSUNDEEP
databaseoracle-databaseplsql
hellomate
hellomate

Reputation: 133

Update each row of a table after I've added a new column Oracle PL/SQL

Documents I store in a database have a unique ID (da_document_id) and shared ID (da_doc_id) between different versions of the same file. Say we added a new version of the same kind of file, da_document_id is unique however, da_doc_id stays the same. I have added a new column to the table and want to populate it with a unique ID of the latest version of that specific file. At this point I am updating all of the records with the latest version of da_doc_id 14493. How do I update it based on different da_doc_ids in the table?

UPDATE da_favourites
SET da_document_id = (SELECT da_document_id
                      FROM   da_documents
                      where  da_version = (SELECT MAX(da_version)
                                           FROM   da_documents
                                           WHERE  da_doc_id = 14493
                                           GROUP BY da_doc_id));

I want it to go through all the da_doc_ids of da_favourites table.

@edit

Table looks like this now. I want to populate each da_document_id with the latest for the specific da_doc_id. One da_doc_id might be shared between da_document_id 11111,22222 etc. Each da_document_id has da_version which originally starts from 1. Select da_document_id of da_doc_id where da_version is the highest.

enter image description here

In the code provided I only update da_document_id for specific da_doc_id 14493. I want to go through all from the image.

Upvotes: 0

Views: 1225

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You probably need something like this (untested).

UPDATE da_favourites o
   SET da_document_id =
          (SELECT da_document_id
             FROM da_documents d
            WHERE     da_version IN (  SELECT MAX (da_version)
                                         FROM da_documents i
                                        WHERE i.da_doc_id = d.da_doc_id
                                     GROUP BY i.da_doc_id)
                  AND d.doc_id = o.da_doc_id);

Upvotes: 1

Related Questions