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 tabl…

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'));
```

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

Solution

Problem

Related problems