Question:
I'm trying (just for a change) to delete week old data from a custom table using open sql and a subquery eg
DELETE FROM zswift_texts
WHERE (ebeln, version, swift_line_no) IN
( SELECT ebeln, version, swift_line_no
FROM zswift_orders
WHERE processed_flag = 'X'
AND processed_date <= w_del_date ).
I've tried various combinations of commas / no commas and brackets / no brackets but it doesn't seem to like any of them. I know there are lots of other ways to do this but today I feel I would prefer this way. Does anyone know if it is possible?
Hi Christmaslights,
what about using "AND" statements?
Think I would try it with "AND" - something like:
DELETE FROM zswift_texts
WHERE ebeln IN
( SELECT ebeln
FROM zswift_orders
WHERE processed_flag = 'X'
AND processed_date <= w_del_date )
AND version IN
(select version
FROM zswift_orders
WHERE ....... )
AND ..... IN
.....
and so on
Well... seems to me that the performance is lousy in this statement but I think it could work?
Sorry..didn't try it out myself..it was just a suggestion while reading your question.
Answer:
Hi. Thanks for your reply. It's an idea....but as you say it would be inefficient since it would be running three subqueries on the same table and it might not do exactly what I want. For example if I have the following in my tables:
Texts Table Orders table
Ebeln Version Line Ebeln Version Line Processed Flag
A 1 XX A 1 XX X
A 2 XX A 2 XX SPACE
B 1 XX B 1 XX SPACE
B 1 YY B 1 YY X
I want to delete texts lines A 1 XX and B 1 YY only and if I use a succession of ANDs as subqueries on the orders table I think I would end up deleting text lines A 1 XX and B 1 XX and B 1 YY which would not be right I really need to run the subquery check on the full key to make it specific.
I guess since open sql is funny about commas that it won't let me do a multiple field subquery, unless there is some other separator that I've never heard of.
Answer:
Blast, it messed up the layout of my data even though it looked alright in print preview. I'll try again....
Texts Table Orders table
Ebeln Version Line Ebeln Version Line Processed Flag
A 1 XX A 1 XX X
A 2 XX A 2 XX SPACE
B 1 XX B 1 XX SPACE
B 1 YY B 1 YY X