A rather tricky one

[citychap26]

Hi All

I am having a nightmare trying to produce what I would've normally done in a stored proc. Here's the problem.

I have created an exclusion table (tblRecNodesDef) for products to be excluded:

ACCOUNT, SRC_SYS, SEGMENT, EXCLUDE, VALUE
13, BND, economic_category, Y, BB
13, BND, economic_category, Y, BC
...

I have a query called exclude_Econ_Cat:

SELECT a.*
FROM tblX AS a, tblRecNodesDef AS b
WHERE a.GLAccount= b.Account
AND b.SRC_SYS='BND'
AND a.Economic_Category = b.VALUE
AND b.SEGMENT = "Economic_Category"
and EXCLUDE = 'Y';

It returns fine. I have the same query above for
Loan_Status, Boe_Category, Unit_ID and a couple of others.

What I want to do is delete from a temp_table based on tblX where the records match whats in exclude_Econ_Cat.

I have tried

Delete from temp_table where exists(select * from exclude_Econ_Cat)

it tried to delete all records in temp_table.

Anyone know how to get this to work?

Cheers

SK

 

[Crowley16]

the where statement you want is:

where SomeID IN(select * from exclude...)

--------------------
Procrastinate Now!

 

[citychap26]

Hmm nice try Crowley, but not good enough... Already tried it. And I would have had to used the following syntax:

....
Where BND_Deal_ID in (select BND_Deal_ID from exclude...)

Cheers

SK

 

[lespaul]

so you are saying that:

DELETE FROM tmpTable Where BND_Deal_ID in (select BND_Deal_ID from exclude...)

this trys to delete records from the tmpTable where the BND_Deal_ID isn't in the exclude table?


Leslie

 

[PHV]

DELETE a.* FROM temp_table a INNER JOIN tblRecNodesDef B
ON a.GLAccount = b.Account AND a.Economic_Category = b.VALUE
WHERE b.SRC_SYS='BND' AND b.SEGMENT = 'Economic_Category' AND b.EXCLUDE = 'Y';

Another way:
DELETE T.* FROM temp_table T INNER JOIN exclude_Econ_Cat E
ON T.GLAccount = E.GLAccount AND T.Economic_Category = E.Economic_Category;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[citychap26]

Hi PHV,

I have tried this one before, however without specifying INNER JOIN and get the following message:

Operation must user an updateable query.

Hi Lesley,

I have tried that one too and it tries to delete everything in the table.

Back to the drawing board. Perhaps Access just does not allow this sort of delete?

Cheers

SK

 

[PHV]

And this ?
DELETE FROM temp_table T
WHERE GLAccount In (SELECT GLAccount FROM exclude_Econ_Cat E WHERE E.GLAccount = T.GLAccount AND E.Economic_Category = T.Economic_Category);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[citychap26]

Hmm, you'd think this would be the best sol, however it is taking years. and the table is indexed on the correct field.

Cheers

SK

 

[PHV]

And this ?
DELETE FROM temp_table a
WHERE a.GLAccount In (SELECT b.Account FROM tblRecNodesDef b WHERE b.Account = a.GLAccount AND b.VALUE = a.Economic_Category AND b.SRC_SYS='BND' AND b.SEGMENT = 'Economic_Category' AND b.EXCLUDE = 'Y');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244