Creating a DELETE script based on a SELECT script

Question

Fairly new to PL/SQL...

I have the following SELECT script which joins two different tables to get the output I require.

I also have a WHERE clause within the sub-query which looks at two separate conditions:

    SELECT p.provider_id, p.prov_name, ecp.*
  FROM ey30h_check_provider ecp
  JOIN provider p
    ON ecp.provider_id = p.provider_id
  JOIN ey30h_check ec
    ON ecp.ey30h_check_id = ec.ey30h_check_id
 WHERE (ecp.ey30h_check_id, ec.dern_number) IN
       (select ec.ey30h_check_id, ec.dern_number
          from ey30h_check ec
         WHERE ec.dern_number = '52365214587'
           AND ecp.provider_id = 50014);

Results to this script can be seen below:

How would I go about creating a DELETE script for this?

I've tried having a little dig at it with the following script, but this displays 'ORA-00918: column ambiguously defined':

DELETE from ey30h_check_provider ecp
WHERE ecp.ey30h_check_id in 
(select ec.ey30h_check_id from ey30h_check ec
JOIN ey30h_check ec ON ecp.ey30h_check_id = ec.ey30h_check_id
JOIN provider p ON ecp.provider_id = p.provider_id
WHERE ec.dern_number = '52365214587'
AND ecp.provider_id = 50014);

Fairly new to PL/SQL so any advise will be appreciated :)

Answer 1

Try using this:

DELETE from ey30h_check_provider ecp
WHERE ecp.ey30h_check_id in
    (SELECT ecp.ey30h_check_id
    FROM ey30h_check_provider ecp
    JOIN provider p
    ON ecp.provider_id = p.provider_id
    JOIN ey30h_check ec
    ON ecp.ey30h_check_id = ec.ey30h_check_id
    JOIN provider p
    ON ecp.provider_id = p.provider_id
        WHERE (ecp.ey30h_check_id, ec.dern_number) IN
        (select ec.ey30h_check_id, ec.dern_number from ey30h_check ec
            WHERE ec.dern_number = '52365214587'
            AND ecp.provider_id = 50014));

'ORA-00918: column ambiguously defined': occurs when, a column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN.

If your select query gives correct output, your delete query shouldn't give you any problem.