Please help w/delete statement 2

[sql99]

Hello everyone,

I have the following statement:

DELETE FROM emp_record
WHERE EXISTS ((SELECT 'X'
FROM emp_record emp, employee_type empt
WHERE emp.emp_cat in ('Recep', 'Secr')
AND empt.emp_stat in ('Mech', 'Elect')
AND empt.term_stat = 'Active'
AND emp.emp_id = empt.emp_num)

UNION

(SELECT 'X'
FROM emp_record emp, employee_type empt
WHERE emp.emp_cat in ('Mech', 'Elect')
AND empt.emp_stat in ('Recep', 'Secr')
AND empt.term_stat = 'Active'
AND emp.emp_id = empt.emp_num))


When I run this, I get a timeout error message - "distributed waiting for lock". Can you please assist?

Thanks in advance,
sql99

 

[SantaMufasa]

SQL99,

I don't know specifically what is causing your "distributed waiting for lock" error message, but I have a couple of issues with the logic in your DELETE statement:

1) I do not understand the purpose of your UNION since the SELECTs in the UNION are identical. Since they are identical, the result sets will be identical, thus causing the UNION to be extraneous.

2) Usually the SELECT of a DELETE...WHERE is "correlated" with the DELETE itself. If it is not "correlated", then either ALL or NONE of the rows in the table will be deleted. I don't believe that is what you want.

Please respond back to my assertions, then we can continue troubleshooting from there.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:55 (10Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:55 (10Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sql99]

Hi Dave,

Thanks for the reply. I guess it was a bad example I had up there. When I do the select alone and put in columns instead of 'X', it works fine. However with the delete and the 'X', it deletes everything out of the emp_record table. I guess I have to have more where conditions matching the columns up from the outside delete statement?

Thanks,
sql99

 

[SantaMufasa]

SQL99,

...it deletes everything out of the emp_record table.

Yes, as I mentioned in my earlier post, that is what I would have expected given your current code. That is happening because your code is not "correlating" any information from the "current" row that Oracle is evaluating for deletion.


I am happy to suggest what your WHERE statement should say if you are willing to confirm (even with pseudo-code) the characteristics of rows you wish to DELETE. Please post the fill-in-the-blank responses to, "I want to DELETE rows when..."

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:08 (11Jan05) UTC (aka "GMT" and "Zulu"),
@ 18:08 (10Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sql99]

Hi Dave,

Thanks for your time on this...Okay, I'll try to be as clear as I can on what I would like to do....

I believe I will need two separate delete statements instead of using a "UNION" on the two selects...

Basically I would like to delete all records from the emp_record table that meet the select conditions below. I know the following deletes all records because I don't have any conditions in the main delete statement. How would I add the conditions in the main delete statement when there's a join to another table?

DELETE FROM emp_record
WHERE EXISTS ((SELECT 'X'
FROM emp_record emp, employee_type empt
WHERE emp.emp_cat in ('Recep', 'Secr')
AND empt.emp_stat in ('Mech', 'Elect')
AND empt.term_stat = 'Active'
AND emp.emp_id = empt.emp_num)


Thanks,
sql99

 

[SantaMufasa]

SQL99,

Following is a method to correlate the "DELETE" record with the "WHERE EXISTS..." records (I've placed the correlation pieces in bold. Also, it appears that you have one-too-many open parens, so I'll remove one):

DELETE FROM emp_record [b]outside[/b]
  WHERE EXISTS (SELECT 'X'
                   FROM emp_record emp, employee_type empt
                  WHERE emp.emp_cat in ('Recep', 'Secr')
                    AND empt.emp_stat in ('Mech', 'Elect') 
                    AND empt.term_stat = 'Active'
                    AND emp.emp_id = empt.emp_num
                    [b]AND outside.emp_id = emp.emp_id[/b])

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:15 (11Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:15 (11Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sem]

I also think that "inner" emp_record is completely redundant, isn't it?

DELETE FROM emp_record emp
WHERE emp.emp_cat in ('Recep', 'Secr')
AND EXISTS (SELECT 'X'
            FROM employee_type empt
            WHERE empt.emp_stat in ('Mech', 'Elect') 
                  AND empt.term_stat = 'Active'
                  AND emp.emp_id = empt.emp_num)

Regards, Dima

http://seminihin.narod.ru

 

[sql99]

Thanks Dave and Dima....I revised the query to use "IN" instead of "EXISTS" (though "EXISTS" would work as well) and I added another "AND" condition - where emp.emp_cat in ('Recep', 'Secr') and I'm getting the result I wanted.

Basically it's just missing what Dave mentioned above...another condition in the outside delete statement.

Thanks again for all your help...

sql99