How to delete values of one table based on multiple values from two tables

Question

I have two tables:

Table 1 = products (id, name, organisationId, subcategoryId)
Table 2 = products_attribute_attributes (productId, attributeId)

I am trying to delete from Table 2 where attributes match a specific attributeId AND relate to products from a number of different organisationIds.

What I have tried is below:

DELETE product_attributes_attribute FROM product_attributes_attribute
INNER JOIN product
ON  product.id = product_attributes_attribute.productId
WHERE attributeId = "154" AND organisationId IN (685,720,773,789,774,834,911,698);

MySQL gave me this response:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

As I don't know MySQL very well I am fairly sure that safe update mode is essential for me to keep enabled to prevent potentially disastrous mistakes so I don't intend to disable it. What I don't understand is why it thinks I haven't used a WHERE clause when you can see that it is there in the code. What am I doing wrong?

What is the primary key in products_attribute_attributes?

Gordon Linoff
– Gordon Linoff

2019-08-17 14:26:46 +00:00
Commented Aug 17, 2019 at 14:26 — Gordon Linoff
– Gordon Linoff, Commented Aug 17, 2019 at 14:26

ScaisEdge · Accepted Answer · 2019-08-17 09:15:46Z

1

seems you have not primary key on your tables so mysql raise the error

DELETE product_attributes_attribute 
FROM product_attributes_attribute
INNER JOIN product ON  product.id = product_attributes_attribute.productId
WHERE attributeId = "154" 
AND organisationId IN (685,720,773,789,774,834,911,698);

you should OR alter you table and ad primary key for

table product
id 

table products_attribute_attributes
productId, attributeId

OR disable SQL_SAFE_UPDATES

SET SQL_SAFE_UPDATES = 0;

edited Aug 17, 2019 at 9:15

answered Aug 17, 2019 at 8:22

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

But don't do option 2

Akhil · Accepted Answer · 2019-08-17 12:27:41Z

0

I believe you are using mysql workbench. The error is because you are modifying data without a key column in the where condition in safe mode. You can disable Safe mode from workbench itself.

Edit-->preference-->SQL editor-->bottom of the page you have checkbox for safe mode(Screenshot attached)

screen

answered Aug 17, 2019 at 12:27

Akhil

232 silver badges7 bronze badges

Comments

Domini · Accepted Answer · 2019-08-20 15:54:01Z

0

In the end I turned off safe mode and the below code worked:

DELETE product_attributes_attribute.* FROM product_attributes_attribute INNER JOIN product ON product.id = product_attributes_attribute.productId WHERE product_attributes_attribute.attributeId = "188" AND organisationId IN (685,720,773,789,774,834,911,698);

answered Aug 20, 2019 at 15:54

Domini

11 bronze badge

Collectives™ on Stack Overflow

How to delete values of one table based on multiple values from two tables

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related