根据另一个表中的值更新表中的列

I have 2 tables : EMP and TMP_EMP which are both identical. I am using Oracle.

我有2个表：EMP和TMP_EMP都是相同的。我正在使用Oracle。

EMP:

EMP：

+----------+----------+
| ID       | ALLOWED  |
+----------+----------+
| 1-xx     |    0     |
| 2-xx     |    1     |
| 3-xx     |    0     |
| 4-xx     |    2     |
+----------+----------+

TMP_EMP:

TMP_EMP：

+----------+----------+
| ID       | ALLOWED  |
+----------+----------+
| 1-xx     |    0     |
| 2-xx     |    0     |
| 4-xx     |    0     |
| 5-xx     |    0     |
+----------+----------+

What I want to do is to update the the field ALLOWED in the TMP_EMP table and set it to the same value of ALLOWED in the EMP table if the employee exist in both tables. In short the final TMP_EMP table should look like this:

我想要做的是更新TMP_EMP表中的ALLOWED字段，如果两个表中都有员工，则将其设置为EMP表中的ALLOWED值。简而言之，最终的TMP_EMP表应该如下所示：

TMP_EMP:

TMP_EMP：

+----------+----------+
| ID       | ALLOWED  |
+----------+----------+
| 1-xx     |    0     |
| 2-xx     |    1     |
| 4-xx     |    2     |
| 5-xx     |    0     |
+----------+----------+

I have written my sql update as follows :

我已经编写了我的sql更新，如下所示：

update TMP_EMP
set ALLOWED = IC.ALLOWED
WHERE ID in (
SELECT IC.ID 
FROM TMP_EMP tmp, EMP IC 
where IC.ID LIKE decode(instr(tmp.ID,'-'),0,tmp.ID,substr(tmp.ID,0,instr(tmp.ID,'-')-1)) || '%');

But this is not working. I would be really grateful for any help here. Thanks.

但这不起作用。我真的很感激这里的任何帮助。谢谢。

2 个解决方案

#1

UPDATE TMP_EMP t1
SET ALLOWED = (SELECT ALLOWED
               FROM EMP t2
               WHERE t2.ID LIKE DECODE(INSTR(t1.ID, '-'), 0, t1.ID,
                                       SUBSTR(t1.ID, 0, INSTR(t1.ID, '-')-1)) || '%')
WHERE EXISTS (SELECT ALLOWED
               FROM EMP t2
               WHERE t2.ID LIKE DECODE(INSTR(t1.ID, '-'), 0, t1.ID,
                                       SUBSTR(t1.ID, 0, INSTR(t1.ID, '-')-1)) || '%')

#2

Not tried in oracle but i would use a CTE to do this.

没有在甲骨文中尝试但我会使用CTE来做到这一点。

;with tmp as (
    SELECT IC.ID 
    FROM TMP_EMP tmp, EMP IC 
    where IC.ID LIKE decode(instr(tmp.ID,'-'),0,tmp.ID,substr(tmp.ID,0,instr(tmp.ID,'-')-1)) || '%');
)
update te
set te.ALLOWED = tmp.ALLOWED
from TMP_EMP te
inner join tmp on tmp.ID = te.ID