Hello! I'm trying to delete orphan rows in a script. What is the best way to do that?

I tryed this with no sucess:
DELETE FROM tbl1 WHERE myCol NOT IN (SELECT id FROM tbl2)

I can get the row I want with LEFT JOIN, but I can't find a way to make a Delete statment with JOIN on it:
SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl2.id = tbl1.myCol WHERE tbl2.id IS NULL

Please help!

Antonio

The syntax use for DELETE in DBISAM using JOIN is not supported in ElevateDB. What you've posted looks ok to me, but what you haven't said is what the problem is.

Do you receive an error message, or is nothing deleted or something else?

Roy Lambert [Team Elevate]

Thank you for your answer. The DELETE .. NOT IN is not deleting the rows. When I try SELECT to see the lines I expected to be deleted I'm getting a null result, one row with all null values.

SQL: SELECT * FROM tbl1 WHERE myCol NOT IN (SELECT id FROM tbl2)

I already tryed to put the colums not nullable, with same results... Thank you!



Roy Lambert wrote:

Antonio

The syntax use for DELETE in DBISAM using JOIN is not supported in ElevateDB. What you've posted looks ok to me, but what you haven't said is what the problem is.

Do you receive an error message, or is nothing deleted or something else?

Roy Lambert [Team Elevate]

Antonio


>Thank you for your answer. The DELETE .. NOT IN is not deleting the rows. When I try SELECT to see the lines I expected to be deleted I'm getting a null result, one row with all null values.

That says that there are no rows which satisfy your selection criteria.

>SQL: SELECT * FROM tbl1 WHERE myCol NOT IN (SELECT id FROM tbl2)

Without your data to look at there's nothing much anyone can do to help with such a simple query. Your result is saying that all of the myCol from tbl1 are also used as id in tbl2.

>I already tryed to put the colums not nullable, with same results... Thank you!

Sorry, don't understand that bit.

Roy Lambert [Team Elevate]

Hi Antonio

I agree with Roy.  
There has to be something wrong with your data, or the tables and
columns you are using.

I have a similar need to weed out orphans and it is working fine.
But it is a little more complicated and so I first build a temporary
table with a list of the IDs to be deleted.
Then I use this (near end of the script):
....
 EXECUTE IMMEDIATE 'DELETE FROM "Divers"
   WHERE "DRef" IN (SELECT d FROM "temp2")';
....

That looks the same as your code except it does not use the NOT

Malcolm

Yep, I know, this seems obvious.

I talked about the NULL columns because I know that in some cases the use of null values can cause NOT IN to output null whenever there is a null value involved. Example:

'dog' NOT IN ['cat', 'rat', null] = null

You can have true, false and null as results instead of only true or false.


I have made a sample db with some data for you to see. Please see the attachment.

Yep, I know, this seems obvious.

I talked about the NULL columns because I know that in some cases the use of null values can cause NOT IN to output null whenever there is a null value involved. Example:

'dog' NOT IN ['cat', 'rat', null] = null

You can have true, false and null as results instead of only true or false.


I have made a sample db with some data for you to see. Please see the attachment.

Try this query:
SELECT * FROM "DBREUT" WHERE "Sala" NOT IN (SELECT "Codigo" FROM "DBSAL")

Antonio


>I have made a sample db with some data for you to see. Please see the attachment.

I assume its unicode because I only have ansi and can't open it.

>Try this query:
>SELECT * FROM "DBREUT" WHERE "Sala" NOT IN (SELECT "Codigo" FROM "DBSAL")

You have to handle potential nulls yourself. You can do it in a number of different ways eg

SELECT * FROM "DBREUT" WHERE Sala IS NULL OR  "Sala" NOT IN (SELECT "Codigo" FROM "DBSAL" WHERE Codigo IS NOT NULL)

or

SELECT * FROM "DBREUT" WHERE  COALESCE("Sala",YYY') NOT IN (SELECT COALESCE("Codigo",'XXX') FROM "DBSAL")

Since I don't know if Sala and Codigo are numeric or alpha I've assumed alpha. You need to set the values for then to be coalesed to so that they won't represent actual values that might be encountered.

Roy Lambert [Team Elevate]

I figured out the problem, bad data! I repair and compress the DB and everything start to work as expected.
Yes, i'm using unicode.

Thank you all for your help.




Roy Lambert wrote:

Antonio


>I have made a sample db with some data for you to see. Please see the attachment.

I assume its unicode because I only have ansi and can't open it.

>Try this query:
>SELECT * FROM "DBREUT" WHERE "Sala" NOT IN (SELECT "Codigo" FROM "DBSAL")

You have to handle potential nulls yourself. You can do it in a number of different ways eg

SELECT * FROM "DBREUT" WHERE Sala IS NULL OR  "Sala" NOT IN (SELECT "Codigo" FROM "DBSAL" WHERE Codigo IS NOT NULL)

or

SELECT * FROM "DBREUT" WHERE  COALESCE("Sala",YYY') NOT IN (SELECT COALESCE("Codigo",'XXX') FROM "DBSAL")

Since I don't know if Sala and Codigo are numeric or alpha I've assumed alpha. You need to set the values for then to be coalesed to so that they won't represent actual values that might be encountered.

Roy Lambert [Team Elevate]