如何根据其他表中的内容更新表ID-s？

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.

我使用的是版本11.2的Oracle数据库。我需要根据其他表中的内容更新表DOCUMENT_SHARING document_id,正确的ID应该由其他唯一标识符选择。一个文档可能有多个共享,或者可能根本不共享。怎么做?我被卡住了。

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,以便结果如下:

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"

错误报告:SQL错误:ORA-01407:无法更新(“SCHEMA”。“DOCUMENT_SHARING”。“DOCUMENT_ID”)为NULL 01407. 00000 - “无法更新(%s)为NULL”

query "descr DOCUMENT_SHARING"

查询“descr DOCUMENT_SHARING”

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

3 个解决方案

#1

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);

#2

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;

#3

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

DOCUMENT.ID空值导致问题。试试这个:

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 ;

#1