I have three tables without primary keys, are linked through certain columns. I’m trying to delete the records of the table1 with same id in table 2, but opi must match in table 2 and 3.
(https://i.stack.imgur.com/7G6wV.png)](https://i.stack.imgur.com/VKP7t.png)
id
must be the same in table1 and table2opi
must be the same in table2 and table3date
are similar in three tablesrsop
are similar in three tables
This is my SELECT
query:
SELECT *
FROM table1 t1, table2 t2, table t3
WHERE t2.opi = t3.opi
AND (t1.date = t2.date AND t1.date = t3.date AND t2.date = t3.date)
AND (T1.rsop = 0 AND T2.rsop = 0 AND T3.rsop = 0);
This query shows 370 results
But when I tried to delete with this code:
DELETE FROM table1 t1
WHERE id IN (SELECT id
FROM table1 t1, table2 t2, table3 t3
WHERE t2.mtm_operationid = t3.mtm_operationid
AND (t1.date = t2.date AND t1.date = t3.date AND t2.date = t3.date)
AND (T1.rsop = 0 AND T2.rsop = 0 AND T3.rsop = 0));
This query deletes 400 rows.
Another attempt:
DELETE FROM
(SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t2.mtm_operationid = t3.mtm_operationid
AND (t1.date = t2.date AND t1.date = t3.date AND t2.date = t3.date)
AND (T1.rsop = 0 AND T2.rsop = 0 AND T3.rsop = 0));
I get:
ORA-01752 error
I tried with a CTE, too, explicit join and with EXIST clause but delete 400, exists delete only 126
Does anyone know why it does not delete the 370 like the select query returns? Is it because of the primary key? How can I fix it? Thank you very much.
Your WHERE clause criteria is different between the select query and the delete query. You should be able to simply cut and paste your select of IDs inside WHERE id in ( –>your select statement here<–)
Thanks for ur answer,was that I made a mistake by copying my own erroneous query, but i edited.
Please use proper
JOIN
. It’s been around for over 30 years!!Thanks for ur advice but i tried!