Help with SQL 1

[alexhu]

I have 2 tables

one containing headers (ahasnf00)

the other containing detail (adasnf00)

I need to purge both, but cannot work out how to purge the detail table.

The connection between the 2 tables is shipment number

as in the following SQL

select b.AD00_SHIPMENT_NBR
from ahasnf00 a, adasnf00 b
where a.AH00_VERIFIED_DATE like '%&DATE'
and
a.AH00_STATUS_CODE >= '50'
and
a.AH00_SHIPMENT_NBR=b.AD00_SHIPMENT_NBR
/

This works OK and gives me the correct numbers, but how do I

a) insert these records into another table
b) delete thenm from the detail table

Alex

 

[Dagon]

Is this what you want to do ?

insert into temp_table(shipment_nbr)
select b.AD00_SHIPMENT_NBR
from ahasnf00 a, adasnf00 b
where a.AH00_VERIFIED_DATE like '%&DATE'
and
a.AH00_STATUS_CODE >= '50'
and
a.AH00_SHIPMENT_NBR=b.AD00_SHIPMENT_NBR
/

delete from adasnf00
where adoo_shipment_nbr in
(select shipment_nbr from temp_table)
/

 

[alexhu]

Looks good - I'll let you know

Alex

 

[sybaseguru]

Alex,

how about this

CREATE TABLE results
AS
SELECT
      b.AD00_SHIPMENT_NBR
FROM
      ahasnf00 a, adasnf00 b
WHERE
      a.AH00_VERIFIED_DATE like '%&DATE'
AND
      a.AH00_STATUS_CODE >= '50'
AND
      a.AH00_SHIPMENT_NBR=b.AD00_SHIPMENT_NBR
/
DELETE
FROM adasnf00 
WHERE (AD00_SHIPMENT_NBR) IN
  (
   SELECT
          a.AD00_SHIPMENT_NBR
   FROM   ahasnf00 a,
          adasnf00 b
   WHERE
          a.AH00_VERIFIED_DATE like '%&DATE'
   AND
          a.AH00_STATUS_CODE >= '50'
   AND
          a.AH00_SHIPMENT_NBR = b.AD00_SHIPMENT_NBR
   )
/

Hope this makes sense

 

[alexhu]

Dagon - fine thanks the principle is there (its a little more involved than I made out, but I have the basics now)

Thanks

Alex