如何在 WHERE 子句中使用 SELECT 语句编写 SQLDELETE 语句？

数据库: Sybase Advantage 11

在寻求数据正常化的过程中，我试图删除从 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')
;

以下是我提出的 DELETE声明:

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

当我尝试运行这个语句时，我不断地得到这个错误:

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.

我也尝试过这样的说法:

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

结果是:

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)

有人能帮助我正确地构造一个 DELETE 查询，从而导致正确的数据被删除吗？

555144 次浏览
小开

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

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.

小开
最佳答案

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

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
小开

Your second DELETE query was nearly correct. Just be sure to put the table name (or an alias) between ABC0 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?

小开

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


提交答案