delete from the main table only if above table is populated with data in BTEQ

General
Enthusiast

delete from the main table only if above table is populated with data in BTEQ

Hi Team,

 

I have a question...I have a scenario where in I have to delete the data from B table only when the A table has the data ( using BTEQ which Runs everyday). There are cases when I get empty file and bteq runs quite fine but BTEQ should exit if the table A doesnot have data before executing the delete statement in B table. So I want to make sure that B is deleted only if A has the data in it.

Tried using EXIST query but not getting the solution any help  plz?

 

 

 


Accepted Solutions
Senior Apprentice

Re: delete from the main table only if above table is populated with data in BTEQ

Hi,

If you're saying "delete from B if A has any data in it" then you could use:

SELECT COUNT(*)
FROM a
HAVING COUNT(*) > 0;
.IF ACTIVITYCOUNT = 0 .QUIT 0;

DELETE FROM B...

This will cause BTEQ to exit with rc=0 if 'A' has no data. As soon as 'A' has any data in it then the DELETE will run.

 

If you're saying "delete rows from B where the key value is in A" then something like:

DELETE FROM b
WHERE key-value-column IN (SELECT key-value-column FROM a);

Another way of processing "delete rows from B where the key value is in A" using EXISTS is something like:

DELETE FROM b
WHERE EXISTS (SELECT *
              FROM a
              WHERE a.key-value-column = b.key-value-column);

I think you need to make it a correlated sub-query to do what you want using EXISTS.

 

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: delete from the main table only if above table is populated with data in BTEQ

Hi,

If you're saying "delete from B if A has any data in it" then you could use:

SELECT COUNT(*)
FROM a
HAVING COUNT(*) > 0;
.IF ACTIVITYCOUNT = 0 .QUIT 0;

DELETE FROM B...

This will cause BTEQ to exit with rc=0 if 'A' has no data. As soon as 'A' has any data in it then the DELETE will run.

 

If you're saying "delete rows from B where the key value is in A" then something like:

DELETE FROM b
WHERE key-value-column IN (SELECT key-value-column FROM a);

Another way of processing "delete rows from B where the key value is in A" using EXISTS is something like:

DELETE FROM b
WHERE EXISTS (SELECT *
              FROM a
              WHERE a.key-value-column = b.key-value-column);

I think you need to make it a correlated sub-query to do what you want using EXISTS.

 

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: delete from the main table only if above table is populated with data in BTEQ

Thank you Dave for you reply. It worked :)