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?
This question is related to
sql
select
where-clause
advantage-database-server
sql-delete
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.
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')
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?
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')
)
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
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.
Source: Stackoverflow.com