SQL - Delete Query with Join
Solved
toxik56
Posted messages
12
Status
Membre
-
kiko -
kiko -
Hello,
I am currently trying to establish a deletion query with a join, but I have only partially achieved a result.
I have two tables:
table1(ContractNum,X1,Y1,Z1)
table2(ContractNum,X2,Y2,Z2)
Thus, my wish would be to delete from table1 all contracts that appear in both table1 and table2.
I have established the following SQL code:
DELETE *
FROM table1
WHERE Table2.ContractNum = Table1.ContractNum;
However, I have created a program where I need to specify the rows to delete by indicating the contract numbers.
Thank you in advance for your help.
Best regards
I am currently trying to establish a deletion query with a join, but I have only partially achieved a result.
I have two tables:
table1(ContractNum,X1,Y1,Z1)
table2(ContractNum,X2,Y2,Z2)
Thus, my wish would be to delete from table1 all contracts that appear in both table1 and table2.
I have established the following SQL code:
DELETE *
FROM table1
WHERE Table2.ContractNum = Table1.ContractNum;
However, I have created a program where I need to specify the rows to delete by indicating the contract numbers.
Thank you in advance for your help.
Best regards
3 réponses
Thank you very much for your reply Morgothal!
I just tested your solution. Because there are two "NumContrat" attributes in table1 and table2, the query fails at line 3. It doesn't understand which table we're talking about despite the FROM on line 4. In short, the code works perfectly except for this detail, thank you so much!
For anyone who might come across this topic, here is the code I'll keep:
Best regards
I just tested your solution. Because there are two "NumContrat" attributes in table1 and table2, the query fails at line 3. It doesn't understand which table we're talking about despite the FROM on line 4. In short, the code works perfectly except for this detail, thank you so much!
For anyone who might come across this topic, here is the code I'll keep:
DELETE * FROM table1
WHERE NumContrat in
(SELECT table1.NumContrat
FROM table1 INNER JOIN table2 ON table1.NumContrat = table2.NumContrat)
Best regards
Well done
See you later