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

User · Accepted Answer

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

User · Answer

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 gt 0  BEGIN     DELETE TOP  10000  FROM ToBeDeleted      SET  r     ROWCOUNT   END GO

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to select

Examples related to where-clause

Examples related to advantage-database-server

Examples related to sql-delete