oracle多表关联修改

首先 建表
表一
CREATE TABLE “SCOTT”.“TF_DORMITORY_TASK_INFO”
( “ID” VARCHAR2(50) NOT NULL ENABLE,
“STAFF_ID” VARCHAR2(50),
“PARENT_ID” VARCHAR2(50),
“TYPE” VARCHAR2(8),
“DESCPTION” VARCHAR2(500),
“AUDIT_STATUS” VARCHAR2(8),
“REMARK” VARCHAR2(500),
“UPDATE_DATE” DATE,
“CREATE_DATE” DATE,
“STATUS” VARCHAR2(8),
CONSTRAINT “PK_TF_DORMNFO_ID5697” PRIMARY KEY (“ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”

表二
CREATE TABLE “SCOTT”.“TR_TASK_OBJ”
( “ID” VARCHAR2(50) NOT NULL ENABLE,
“TASK_ID” VARCHAR2(50),
“OBJ_ID” VARCHAR2(50),
“OBJ_TYPE” VARCHAR2(20),
“AUDIT_STATUS” VARCHAR2(8),
“REMARK” VARCHAR2(500),
“CREATE_DATE” DATE,
“STATUS” VARCHAR2(8),
CONSTRAINT “PK_TR_TASK_OBJ_ID” PRIMARY KEY (“ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”

sql语句

UPDATE TF_DORMITORY_TASK_INFO t
SET t.“AUDIT_STATUS” = ‘p’
WHERE
EXISTS ( SELECT 1 FROM TR_TASK_OBJ o WHERE o.“TASK_ID” = t.“ID” AND o.“OBJ_ID” = ‘c4a0263a-2680-4d39-bf83-d9f0adf1afc5’ )