0

I am running a query directly in MySQL Workbench. It is a work in progress, but I can't get past this issue.

The query is as follows:

#Temporary table to hold dcsIds that are past grace period
CREATE TABLE pastGracePeriod (
    dcsId varchar(50) NOT NULL
);

INSERT INTO pastGracePeriod
SELECT dcsId FROM loyaltyOptOutGracePeriod
WHERE optOutDate <= ADDDATE(CURRENT_TIMESTAMP(), INTERVAL -15 DAY);

#temporary table to hold dcsIds that are validated against subscriptions table
CREATE TABLE validatedDcsIds (
    dcsId varchar(50) NOT NULL
);

INSERT INTO validatedDcsIds
SELECT subscriptions.dcsId 
FROM subscriptions
INNER JOIN pastGracePeriod ON subscriptions.dcsId = pastGracePeriod.dcsId
WHERE subscriptions.optOutDate <= ADDDATE(CURRENT_TIMESTAMP(), INTERVAL -15 DAY)
    AND subscriptions.subscriptionId IN (24,25,30)
    AND subscriptions.optInStatus='N';

#delete items in validatedDcsIds table from externalId table
DELETE FROM externalId
WHERE EXISTS (SELECT dcsId FROM validatedDcsIds
    WHERE externalId.dcsId = validatedDcsIds.dcsId);

DROP TABLE pastGracePeriod;
DROP TABLE validatedDcsIds;

The query takes dcsIds (PK) from a table called loyaltyOptOutGracePeriod, validates them against another table called subscriptions, then takes those validated dcsIds and deletes them from a table called externalId.

The query works fine until it gets to the DELETE clause. In the externalId table, the PK is a composite key consisting of fields dcsId, appId, and appName. As it is written above, I get Error 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

If I change it to:

DELETE FROM externalId
WHERE EXISTS (SELECT dcsId FROM validatedDcsIds
    WHERE externalId_backup.dcsId = validatedDcsIds.dcsId)
AND appId <> NULL;

I get no errors, but nothing gets deleted from the externalId table as expected.

I am at a loss, any suggestions are very welcome!

NOTE: I do not need to know how to disable safe update mode.

Grace Blackwell
  • 11
  • 1
  • *I get no errors* I don't believe you. None table in your DELETE query contains `appId` column, hence its execution must produce "unknown column" error. – Akina May 05 '22 at 19:06
  • 1
    @Akina appId is a column in the externalId table as part of the composite key. I do not get an error when I run it with that constraint. Why would I lie? – Grace Blackwell May 05 '22 at 19:19
  • Unfortunately not enough info, see [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9686b8b0db31aff6dd8312a00f0adc1f) – Luuk May 05 '22 at 19:27
  • Provide CREATE TABLE and INSERT INTO which allows to reproduce your issue (or create online fiddle). – Akina May 05 '22 at 19:51
  • *`AND appId <> NULL`* - this is not correct. Either use IS NOT NULL or null-safe compare operator. – Akina May 05 '22 at 19:52

1 Answers1

0

Do not use WHERE EXISTS. Use Multiple-Table Syntax:

DELETE table1
FROM table1
JOIN table2 ON {joining conditions}
WHERE {additional conditions}
Akina
  • 39,301
  • 5
  • 14
  • 25