Linked delete query with a Max (1 Viewer)

peterpann88

New member
Local time
Today, 18:33
Joined
Jan 30, 2015
Messages
1
Hi there

I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell

I currently have this:

DELETE DISTINCTROW STM.*, STM.IDNum
FROM dpl_00c_tbl_StmCellDups
INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel
WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));

It doesn't want to throw out any values when i run it, or view it. Am i doing something wrong?

Appreciate any help :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:33
Joined
Feb 19, 2013
Messages
16,553
There is a definite problem with your subquery

(select max(IDNum) from `STM`)

a) you should not put quote marks around the table name - use square brackets if required
b) since this table is in your main query you also need to alias it so

(select max(IDNum) from STM AS T)

Not tested, but don't think you need DISTINCTROW which normally applies to SELECT

And I see no reason for the inner join - if the max IDnum is not in a record linked to dpl_00c_tbl_StmCellDups then nothing will be returned anyway. Perhaps your subquery should be referencing dpl_00c_tbl_StmCellDups rather than STM?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:33
Joined
Jan 20, 2009
Messages
12,849
As soon as you start aggregating records, databases lose track of individual records and can't process them, making the query non-updateable.

The DistinctRow could be a problem.

Sometimes problems with deleting records in joins can be worked around by updating a field to an out of scope value to flag the record and then running a delete on the flag.
 

Users who are viewing this thread

Top Bottom