How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

Question

Database: Sybase Advantage 11

On my quest to normalize data, I am trying to delete the results I get from this SELECT statement:

SELECT tableA.entitynum
FROM tableA q
INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR q.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date')
;

This is the DELETE statement I have come up with:

DELETE FROM tableA
WHERE (SELECT q.entitynum
FROM tableA q
INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR q.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date'))
;

I continuously get this error when I try to run this statement:

ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = S0000;   NativeError = 2124;
[iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: = Boolean value
cannot be operated with non-Boolean value.

I have also tried this statement:

DELETE FROM tableA 
INNER JOIN tableB u on (u.qlabel = tableA.entityrole AND u.fieldnum = tableA.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR tableA.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date')
;

Which results in:

ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = 42000;   NativeError = 2117;
[iAnywhere Solutions][Advantage SQL Engine] Unexpected token: INNER -- Expecting semicolon.
-- Location of error in the SQL statement is: 23 (line: 2 column: 1)

Could someone aid me in properly constructing a DELETE query that will result in the proper data being removed?

Fabe

Worst case scenario - can you create a temporary table, SELECT into that temporary table, do your delete joining the temporary table, then DROPing the temporary table? – Watthour

Answer 1

You need to identify the primary key in TableA in order to delete the correct record. The primary key may be a single column or a combination of several columns that uniquely identifies a row in the table. If there is no primary key, then the ROWID pseudo column may be used as the primary key.

DELETE FROM tableA
WHERE ROWID IN 
  ( SELECT q.ROWID
    FROM tableA q
      INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
      AND (u.FldFormat = 'Date'));

Offprint

Thank you Alex for the quick response. @AlexW I think you hit the nail on the head. The way that this table works is that the entitynum is related to many records and as such is not a primary key in and of itself. I have no experience with using the ROWID psuedocolumn in Advantage. Could you provide explanation on how to use this? Thanks again. – Fabe

Hi, Maybe you have already found information regarding the ROWID, but the below article was helpful for me to understand the functionality behind this pseudocolumn. devzone.advantagedatabase.com/dz/webhelp/Advantage10/… – Multitude

so if the primary key is a set of columns, I can simply them comma separated instead of ROWID? – Opah

It is more complicated with a set of columns. If the DB server supports "row type" as a data type, you can do it with something like WHERE ROW (c1, c2, ..) IN (SELECT ROW (x, y, z) FROM ...). However, if the DB does not support ROW type, then the columns must be converted into a single value expression, for example - concatenation of columns. – Offprint

too bad there's no ROWID if you're using GROUP BY – Rightism

ROWID is an identifier of a single column so it's logical that it is not applicable with GROUP BY. However, you can use Min(ROWID) to get the identifier of one of the rows in the group. – Offprint

Answer 2

Your second DELETE query was nearly correct. Just be sure to put the table name (or an alias) between DELETE and FROM to specify which table you are deleting from. This is simpler than using a nested SELECT statement like in the other answers.

Corrected Query (option 1: using full table name):

DELETE tableA
FROM tableA
INNER JOIN tableB u on (u.qlabel = tableA.entityrole AND u.fieldnum = tableA.fieldnum) 
WHERE (LENGTH(tableA.memotext) NOT IN (8,9,10)
OR tableA.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date')

Corrected Query (option 2: using an alias):

DELETE q
FROM tableA q
INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR q.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date')

More examples here:
How to Delete using INNER JOIN with SQL Server?

Cynthia

Answer 3

Shouldn't you have:

DELETE FROM tableA WHERE entitynum IN (...your select...)

Now you just have a WHERE with no comparison:

DELETE FROM tableA WHERE (...your select...)

So your final query would look like this;

DELETE FROM tableA WHERE entitynum IN (
    SELECT tableA.entitynum FROM tableA q
      INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
      AND (u.FldFormat = 'Date')
)

Claiborn

thank you for the response. It seems as though when I run a count of what I was going to delete I end up with far more records than originally intended (4598021 instead of 32061). I have definitely reach my level of expertise on this. Do you know the reason this would be the case? My count statement looked like this : SELECT COUNT(*) FROM tableA WHERE entitynum IN (SELECT q.entitynum FROM tableA q INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%') AND (u.FldFormat = 'Date')) ; – Fabe

Answer 4

in this scenario:

DELETE FROM tableA
WHERE (SELECT q.entitynum
FROM tableA q
INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR q.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date'));

aren't you missing the column you want to compare to? example:

DELETE FROM tableA
WHERE entitynum in (SELECT q.entitynum
FROM tableA q
INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
OR q.memotext NOT LIKE '%/%/%')
AND (u.FldFormat = 'Date'));    

I assume it's that column since in your select statement you're selecting from the same table you're wanting to delete from with that column.

Naomanaomi

thank you for the response. It seems as though when I run a count of what I was going to delete I end up with far more records than originally intended (4598021 instead of 32061). I have definitely reach my level of expertise on this. Do you know the reason this would be the case? My count statement looked like this : SELECT COUNT(*) FROM tableA WHERE entitynum IN (SELECT q.entitynum FROM tableA q INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%') AND (u.FldFormat = 'Date')) ; – Fabe

The large count is certainly possible if entitynum is not unique (or primary key) in TableA. Fore example, there may be several rows with entitynum equals to 1 but only one of them match the join condition. However the condition 'entitynum IN ( SELECT ... )' will return all of the rows with entitynum equals 1. – Offprint

The select that I had been using was the following to grab all of the columns associated with the the records, the primary key is still the q.entitynum: SELECT q.* FROM tableA q INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%') AND (u.FldFormat = 'Date') – Fabe

Answer 5

Did something like that once:

CREATE TABLE exclusions(excl VARCHAR(250));
INSERT INTO exclusions(excl)
VALUES
       ('%timeline%'),
       ('%Placeholders%'),
       ('%Stages%'),
       ('%master_stage_1205x465%'),
       ('%Accessories%'),
       ('%chosen-sprite.png'),
('%WebResource.axd');
GO
CREATE VIEW ToBeDeleted AS 
SELECT * FROM chunks
       WHERE chunks.file_id IN
       (
       SELECT DISTINCT
             lf.file_id
       FROM LargeFiles lf
       WHERE lf.file_id NOT IN
             (
             SELECT DISTINCT
                    lf.file_id
             FROM LargeFiles lf
                LEFT JOIN exclusions e ON(lf.URL LIKE e.excl)
                WHERE e.excl IS NULL
             )
       );
GO
CHECKPOINT
GO
SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r>0

BEGIN
    DELETE TOP (10000) FROM ToBeDeleted;
    SET @r = @@ROWCOUNT  
END
GO

Puffy

Answer 6

In my project, I used to remove duplicates. (В своем проекте использовал для удаления дублей)

DELETE 
FROM
    a_send 
WHERE
    send_id IN (
            SELECT
        PR.dd
    FROM
        ( SELECT MAX(send_id) as dd, COUNT ( 1 ) AS qty, phone_id FROM "a_send" GROUP BY phone_id ) AS PR 
    WHERE
        PR.qty > 1 
    ) 

Pay attention in order not to delete all records in the principality, use MAX (send_id)
(обратите внимание для того чтобы в приниципе не удалилесь все записи пользовал MAX(send_id))

Disclaimer

Can you please translate your text to English!? – Lovmilla