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?
You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.
DELETE Syntax Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
Example - DELETE Statement with One Condition If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.
Warning: The WHERE clause in the DELETE statement specifies which record or records should be deleted. It is however optional, but if you omit or forget the WHERE clause, all the records will be deleted permanently from the table.
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'));
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With