SQL - how to delete from one table while refering fom other table?

Question

how can i delete from table A while comparing two fields A.P and A.C to table B's B.P and B.C while looking for all matches of B.R = 1 ?

actually neither of the following work but it should go into the direction, unfortunately i can't figure out how...

DELETE FROM A WHERE (A.P = B.P AND A.C = B.C where B.C = 1)

DELETE FROM A WHERE (SELECT B.P, B.C FROM B WHERE B = 1)

Answer 1

DELETE FROM A
FROM A INNER JOIN B ON A.P = B.P AND A.C = B.C
WHERE B.C = 1

The double FROM sometimes throws people off.

Answer 2

DELETE FROM A WHERE A.Id IN 
(SELECT A.Id FROM A INNER JOIN B ON A.P = B.P WHERE B.C = 1)

Answer 3

You are asking to delete all records from A That have a C value = to the C Value in the row in table B that has C = 1 and the same P value??

That's the same as deleting all rows in A That have C value = 1 [and the same P Value in Table B].

so Try this:

Delete A 
Where C = 1 
   And Exists
      (Select * From B
       Where C = 1 
         And P = A.P)

Answer 4

something like this would work

DELETE FROM A
FROM A
 INNER JOIN B ON A.P = B.P AND A.C = B.C AND B.R = 1

Answer 5

DELETE
FROM    A
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   b.p = a.p
                AND b.c = a.c
                AND b.r = 1
        )