View original (French)

SQL - Delete Query with Join

Solved
toxik56 -  
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 Author
    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
      Yes, that's exactly what needs to be done :)
      Well done

      See you later
      0
    2. albamak
      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 Author
    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
    1. Morgothal
      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