Deleting all the items without UPCs

regulatre

Member
We're trying to delete "all items in the database that don't have a UPC associated with them". Our primary key is "item#" and the tables are PUB.item and PUB.upc. items are in the item table, and upcs are in the upc table. each UPC has an item number associated to it in the "item#" field.

I can look at the items and UPCs with this query:

SELECT PUB.item."item#",PUB.item.descrpt,PUB.UPC."upc#"
FROM PUB.item
LEFT OUTER JOIN PUB.UPC on PUB.UPC."item#" = PUB.item."item#"

However I cannot seem to turn that query into a "delete all records with a blank "upc#" query.

Help!?
 

jongpau

Member
Is SQL the only way you could be doing this? Or can you use the 4GL as well?

If you can use the 4GL you could do something like:

FOR EACH item EXCLUSIVE-LOCK WHERE
NOT CAN-FIND(FIRST upc WHERE upc.item# EQ item.item#):
DELETE item.
END.

There are some possible variants to the above and the best way depends on your database design/indices.

It is of course advisable to first run this without the DELETE and to use a DISPLAY of the item number (and any other useful information) instead so you can check whether it does what you want it to do, how long the procedure will take, how many records will be deleted etc. Oh and of course always make a backup before you run something like this :awink:
 

regulatre

Member
Thank you for the response.

I will use your query to get the job done, although I still wonder how the query would look in SQL.... It is definitely possible.
 

jongpau

Member
Oh, I am sure there will be some way of doing it with SQL, but why make your life difficult when you can use a good old for each in the 4GL ? :biggrin:
 

regulatre

Member
I'm not a 4gl devleoper. I'm a support specialist trying to help a customer get the most out of their software.
The tools that I have access to are SQL based. (no Progress development license =(
 
Top