View original (French)

SQL - Delete Query with Join

Solved
toxik56 Posted messages 12 Status Member -  
 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

3 answers

  1.
    toxik56 Posted messages 12 Status Member 4
     
    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: 

    DELETE * FROM table1 
    WHERE NumContrat in
    (SELECT table1.NumContrat 
    FROM table1 INNER JOIN table2 ON table1.NumContrat = table2.NumContrat)

    Best regards
    3
    1. Morgothal Posted messages 1350 Status Member 184
       
      Yes, that's exactly what needs to be done :)
      Well done

      See you later
      0
    2. albamak Posted messages 1 Status Member
       
      ce exilent, your answer Morgothal
      0
    3. angeltlse
       
      Thank you very much, I was able to solve my problem after extensive research.
      0
    4. kiko
       
      Thank you soooo much!
      0
  2. toxik56 Posted messages 12 Status Member 4
     
    I also tried the code above, without success..

    DELETE FROM table1
    USING table1 LEFT JOIN table2 ON (table1.NumContrat = table2.NumContrat)
    WHERE table1.NumContrat = table2.NumContrat ;

    A little help would be welcome..

    Thank you in advance.

    Best regards
    0
  3. Morgothal Posted messages 1350 Status Member 184
     
    Hello,
    I'll do it like this: 

    DELETE * FROM table1 WHERE NumContrat in (SELECT NumContrat FROM table1 INNER JOIN table2 ON table1.NumContrat = table2.NumContrat)


    See you!

    --
    -------------------
    Best regards,
    Clément
    0