Oracle数据库中如何查询，删除多表关联数据
要根据条件删掉⼀⾏数据（根据单号及采购组织名称）
例如要删这⼀⾏ ：Index_number = 0000000032
先根据条件查询到这⼀条数据
SELECT * FROM JY_SUPASS_FLOW flow LEFT JOIN JYPURORG purorg ON flow.PUR_ORG_ID = purorg. ID
WHERE INDEX_NUMBER = '0000000032' AND (purorg. ID IN (SELECT purorg. ID FROM JYPURORG purorg
WHERE PURORG_NAME = '重庆⼤江⽣产物料采购组织'));
接着执⾏删除语句：delete from jy_supass_flow where id = '1000250';
[SQL]delete from jy_supass_flow where id = ‘1000250’
[Err] ORA-02292: integrity constraint (YAPP_SRM.FKRN3EIDP3AA8ALWA2HBBVAMT27) violated - chil
d record found
本来以为利⽤⼯具到这⼀⾏数据直接删除掉就可以了，奈何关联关系太多(别的表中有⽤到这⼀⾏的数据)… 删不掉
数据库中表关联如下：
其他表中关联到该表中数据：
更为糟糕的是 jy_supass_result表还是⼀个主表，它还关联了jy_supass_result_dt明细表，所以如果要删除的话，得⼀次性删三张表，依次是jy_supass_result_dt，jy_supass_result，jy_supass_flow。
所以⾸先要依次查出这三张表中的数据。因为这些表中还有其他关联关系，所以我就直接上sql了
⾸先是jy_supass_result_dt(可省)
SELECT
*
FROM
JY_SUPASS_RESULT_DT dt
LEFT JOIN JY_SUPASS_RESULT result ON dt.sup_ass_result_id = result. ID WHERE
result. ID IN (
SELECT
result. ID
FROM
JY_SUPASS_RESULT result
LEFT JOIN JY_SUPASS_Flow aaa ON result.sup_ass_flow_id = aaa. ID
WHERE
aaa. ID IN (
SELECT
flow. ID
FROM
JY_SUPASS_Flow flow
LEFT JOIN JYPURORG purorg ON flow.pur_org_id = purorg. ID
WHEREoracle数据库怎么查询表
purorg. ID = '1000006'
)
AND (
result. ID IN (
SELECT
result. ID
FROM
JY_SUPASS_RESULT result
LEFT JOIN jy_supass_task task ON result.sup_ass_task_id = task. ID
WHERE
TASK.INDEX_NUMBER = '0000000032'
)
)
);
然后是jy_supass_result
SELECT DISTINCT
(aaa. ID)
FROM
JY_SUPASS_RESULT result
LEFT JOIN JY_SUPASS_Flow aaa ON result.sup_ass_flow_id = aaa. ID WHERE
aaa. ID IN (
SELECT
flow. ID
FROM
JY_SUPASS_Flow flow
LEFT JOIN JYPURORG purorg ON flow.pur_org_id = purorg. ID
WHERE
purorg. ID = '1000006'
)
AND (
result. ID IN (
SELECT
result. ID
FROM
JY_SUPASS_RESULT result
LEFT JOIN jy_supass_task task ON result.sup_ass_task_id = task. ID
WHERE
TASK.INDEX_NUMBER = '0000000032'
)
);
最后依次按照删jy_supass_result_dt，jy_supass_result，jy_supass_flow这样的顺序就可以删掉啦。
不过 写delete语句的时候发现 根本就不需要查询jy_supass_result_dt表中的数据，可以直接通过主表.id来删除明细中的数据。
DELETE FROM JY_SUPASS_RESULT_DT WHERE SUP_ASS_RESULT_ID = '1000057';
DELETE FROM JY_SUPASS_RESULT WHERE id = '1000057';
DELETE FROM jy_supass_flow where id = '1000250';
ps：上⾯是这种是针对多表中关联关系混乱的查询语句。
如果⼀般查询的话，可以参照如下sql：
-- ⼀般写法
select * from jy_supass_flow where id = '10000';
--返回两个集合
SELECT * from JY_SUPASS_FLOW flow, JY_SUPASS_RESULT result WHERE FLOW.id = RESULT.SUP_ASS_FLOW_ID;
--使⽤join
SELECT * from JY_SUPASS_FLOW flow join JY_SUPASS_RESULT result ON FLOW.id = RESULT.SUP_ASS_FLOW_ID; -- WHERE
--⼦查询
-- 返回多条⽤ IN
SELECT flow.* from JY_SUPASS_FLOW flow WHERE id IN (SELECT result.id from JY_SUPASS_RESULT result);
--返回⼀条
SELECT flow.* from JY_SUPASS_FLOW flow WHERE id IN (SELECT result.id from JY_SUPASS_RESULT result where id=1000004);
同事还告诉了⼀个可以查外键关联的sql，多表关联中可以使⽤：
SELECT
A .constraint_name,
A .table_name,
FROM
user_constraints A,
user_constraints b
WHERE
A .constraint_type = 'R'
straint_type = 'P'
AND A .r_constraint_name = b.constraint_name
AND A .constraint_name = 'FKCICV93OTPOKDYGVI0GBB9FL7J';
如果有更好的建议可以分享⼀下。
如有错误 希望指出(ps：⼩⽩⼀枚)。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。