Easy record deletion routine required

[godzuki]

Is there an easy routine in say SQL that does this rather that right a little bit of RPG.

If record X in File A is in File B then delete record X

Thanks

 

[jmd0252]

you could always do a temporary dfu,,, delete the record that way...

 

[flapeyre]

DELETE FROM file
WHERE condition

Me transmitte sursum, Caledoni!

 

[Mercury2]

godzuki,

When you say
'If record X in File A is in File B then delete record X'

Do you mean the complete record in FileB or only some fields that exist also in FileB ?

1/ If complete record, I fear that you'd better write a little bit of RPG, or ... concatenate all fields of FileA to be compared to the concatenation of all fields of FileB like in the example below.

2/ with some fields only, it can be directly carried out in interactive SQL :
- Concatening 2 alphameric fields:
Delete From FileA a Where a.MyFld1||a.MyFld2 =
( Select b.MyFld1||b.MyFld2 from FileB where b.MyFld1||b.MyFld2 = a.Myfld1||a.Myfld2 )

 

[godzuki]

Excellent, i'm only matching a few fields so it should work... is that exactly the syntax of how i type it? i take it if i want to match 3 fields i just ||field3 again??

 

[Mercury2]

yes, that is
Delete From FileA a Where a.MyFld1||a.MyFld2||a.MyFld3 =
( Select b.MyFld1||b.MyFld2||b.MyFld3 From FileB Where b.MyFld1||b.MyFld2||b.MyFld3 = a.Myfld1||a.Myfld2||a.MyFld3 )

If MyFld3 numeric, type
||digits(a.MyFld3) and ||digits(b.MyFld3)

 

[godzuki]

Thanks...so do you mean exactly this?

Delete From SalesHist a Where a.cust_code||product_code = (Select b.cust_code||product_code From Saleshist2.....etc

and if so how do i put an If into the equation..

ie where a.field=b.field and b.field=0

 

[godzuki]

or how do i do this...

if record x in file a = record x in file b then record y in file a = (record y in file a - record y in file b)

 

[Mercury2]

Delete From SalesHist a Where a.cust_code||a.product_code = (Select b.cust_code||b.product_code From Saleshist2 b
where b.field = a.field and b.field = 0 )

However, since b.field must be equal to a.field and b.field must be equal to 0, a.field is equal to 0 too. Therefore the delete stm will be :

Delete From SalesHist a
Where a.cust_code||a.product_code =
( Select b.cust_code||b.product_code From Saleshist2 b
Where b.field = 0 )
And a.field = 0

As a precaution, give it a try first replacing 'Delete' by 'Select *'

 

[Mercury2]

Oops, I made a mistake.

Delete From SalesHist a
Where a.cust_code||a.product_code =
( Select b.cust_code||b.product_code From Saleshist2 b
Where b.cust_code||b.product_code =
a.cust_code||a.product_code
And b.field = 0 )
And a.field = 0