如何在WHERE子句中使用SELECT语句编写SQL DELETE语句?

Database: Sybase Advantage 11

数据库:11 Sybase的优势

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

在我寻求数据正常化的过程中，我试图删除从这个SELECT语句中得到的结果:

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?

有人能帮助我正确地构造一个删除查询，从而删除适当的数据吗?

5 个解决方案

#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.

您需要确定表a中的主键，以便删除正确的记录。主键可以是单个列或几个列的组合，这些列惟一地标识表中的一行。如果没有主键，那么ROWID pseudo列可以用作主键。

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

#2

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

#3

I tweaked your 2nd query to make it work. I think this is more straightforward than using a nested SELECT statement like in the other answers.

我调整了你的第二个查询，使它工作。我认为这比在其他答案中使用嵌套的SELECT语句更简单。

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 detail here:
How to Delete using INNER JOIN with SQL Server?

这里有更多细节:如何使用SQL Server的内部连接来删除?

#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.

我假设它是那个列，因为在你的select语句中，你选择的是你想要从那个列中删除的同一个表。

#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

#1

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

#2