Databases
StackOverflow2013
Postsdbo
POHow to update table ID-s based what is in other table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to update table ID-s based what is in other table?
    text
    copied!<p>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.</p> <p>Here are the table examples:</p> <pre><code>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 </code></pre> <p>I want to update table DOCUMENT_SHARING document_id so that the result would be like this:</p> <pre><code>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 </code></pre> <p>I have tried following:</p> <pre><code>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; </code></pre> <p>But I get error:</p> <blockquote> <p>Error report:<br> SQL Error: ORA-01407: cannot update ("SCHEMA"."DOCUMENT_SHARING"."DOCUMENT_ID") to NULL<br> 01407. 00000 - "cannot update (%s) to NULL"</p> </blockquote> <p>query "descr DOCUMENT_SHARING"</p> <pre><code>Name Null Type ---------------- -------- -------------- DOCUMENT_ID NOT NULL NUMBER(12) USER_CODE NOT NULL VARCHAR2(50) DOC_ID NUMBER(12) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload