Delete Query Criteria (1 Viewer)

NearImpossible

Registered User
Joined
Jul 12, 2019
Messages
149
When trying to run the following delete query (Screen shot attached), I am getting "Specify the table containing the records you want to delete"

It is due to the Criteria entries and I don't know how to fix it.

Please advise

thank you
 

Attachments

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,690
Post the SQL statement, not just image of design.

It is not necessary to list table fields in DELETE action because the entire record is deleted.

DELETE FROM table WHERE some criteria here;

Perhaps you should do like:

DELETE FROM dbo_db_AddNewTicket WHERE TicketID IN (SELECT TicketID FROM Temp);

Why would TecnicianID be needed? Can each ticket have more than one technician associated? Don't see how if TicketID is primary key in main table.
 
Last edited:

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,363
That should be:
Code:
DELETE [B][COLOR="DarkRed"]*[/COLOR][/B] FROM TableName WHERE some condition
OR if more than one table is involved in the query, something like:
Code:
DELETE [B][COLOR="DarkRed"]DISTINCTROW *[/COLOR][/B]  FROM TableName LEFT JOIN OtherTableName ON (TableName.FieldName =  OtherTableName.OtherFieldName WHERE some condition)
 
Last edited:

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,690
Just as explicitly listing fields is not needed, neither is wildcard. Access query builder will throw it in if query object is saved (just as it will add bunches of unnecessary parens) but the action will run just fine without.
 

NearImpossible

Registered User
Joined
Jul 12, 2019
Messages
149
Post the SQL statement, not just image of design.

It is not necessary to list table fields in DELETE action because the entire record is deleted.

DELETE FROM table WHERE some criteria here;

Perhaps you should do like:

DELETE FROM dbo_db_AddNewTicket WHERE TicketID IN (SELECT TicketID FROM Temp);

Why would TecnicianID be needed? Can each ticket have more than one technician associated? Don't see how if TicketID is primary key in main table.


DELETE FROM dbo_db_AddNewTicket WHERE TicketID IN (SELECT TicketID FROM Temp); worked like a charm, and I was using both columns in another query that I copied to make the delete query, but you are correct in stating that TechnicanID is not needed
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,363
Just as explicitly listing fields is not needed, neither is wildcard. Access query builder will throw it in if query object is saved (just as it will add bunches of unnecessary parens) but the action will run just fine without.
I was assuming the delete query would be saved
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom