CODEWITHSUNDEEP
sqloracle-database
Marko
Marko

Reputation: 1617

How to update table ID-s based what is in other table?

I am using version 11.2 Oracle database. I need to update table DOCUMENT_SHARING document_id's based what's in other table, the correct ID should be selected by other unique identifier. One document may have more than one sharing, or maybe not at all. How to do it? I'm stuck.

Here are the table examples:

DOCUMENT
id  |   text    |   doc_id
--------------------------
6   |   foo1    |   1021
120 |   foo2    |   1123
123 |   foo3    |   1429
121 |   foo4    |   1527
998 |   foo5    |   1722
542 |   foo6    |   1923

DOCUMENT_SHARING
document_id |   shared_to   |   doc_id
--------------------------------------
            |   human1      |   1021
            |   human2      |   1123
            |   human3      |   1429
            |   human4      |   1527
            |   human5      |   1722
            |   human6      |   1923

I want to update table DOCUMENT_SHARING document_id so that the result would be like this:

DOCUMENT_SHARING
document_id |   shared_to   |   doc_id
--------------------------------------
6           |   human1      |   1021
120         |   human2      |   1123
123         |   human3      |   1429
121         |   human4      |   1527
998         |   human5      |   1722
542         |   human6      |   1923

I have tried following:

UPDATE DOCUMENT_SHARING DS SET DS.document_id = (SELECT D.ID FROM DOCUMENT D WHERE D.remote_application = 'DMS' AND D.doc_id IS NOT NULL AND D.doc_id = DS.doc_id) where DS.doc_id IS NOT NULL;

But I get error:

Error report:
SQL Error: ORA-01407: cannot update ("SCHEMA"."DOCUMENT_SHARING"."DOCUMENT_ID") to NULL
01407. 00000 - "cannot update (%s) to NULL"

query "descr DOCUMENT_SHARING"

Name             Null     Type           
---------------- -------- -------------- 
DOCUMENT_ID      NOT NULL NUMBER(12)     
USER_CODE        NOT NULL VARCHAR2(50)       
DOC_ID                    NUMBER(12)

Upvotes: 1

Views: 372

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

The DOCUMENT.ID null values are causing the trouble. Try this one:

UPDATE 
  ( SELECT DS.document_id, D.id
    FROM DOCUMENT_SHARING DS
      JOIN DOCUMENT D 
        ON DS.doc_id = D.doc_id
    WHERE D.remote_application = 'DMS' 
  ) tmp
SET tmp.document_id = tmp.id 
WHERE tmp.id IS NOT NULL ;

Upvotes: 0

wweicker
wweicker

Reputation: 4962

UPDATE DOCUMENT_SHARING ds
   SET ds.document_id = (SELECT MAX(d.id)
                           FROM DOCUMENT d
                          WHERE d.remote_application = 'DMS'
                            AND d.doc_id = ds.doc_id);
 WHERE ds.doc_id IS NOT NULL;

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

Like this:

Update DOCUMENT_SHARING DS
set document_id = (
  Select D.id 
  from  DOCUMENT
  where DS.doc_id = D.doc_id)

I think that wher you say:

UPDATE DOCUMENT_SHARING DS 
SET DS.document_id = 
  (SELECT D.ID FROM DOCUMENT D 
   WHERE D.remote_application = 'DMS' 
   AND D.doc_id IS NOT NULL AND DS.doc_id = D.doc_id) 
where DS.doc_id IS NOT NULL;

You want to say:

UPDATE DOCUMENT_SHARING DS 
SET DS.document_id = 
  (SELECT D.ID FROM DOCUMENT D 
   WHERE D.remote_application = 'DMS' 
   AND D.doc_id IS NOT NULL AND DS.doc_id = D.doc_id) 
where exists
  (SELECT D.ID FROM DOCUMENT D 
   WHERE D.remote_application = 'DMS' 
   AND D.doc_id IS NOT NULL AND DS.doc_id = D.doc_id);

Upvotes: 2

Related Questions