2

I have been getting this error message from mySQL

"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. "

The query I use is this:

 UPDATE table1 a INNER JOIN table2 asa ON a.ID = asa.Table1Id SET a.ReferenceID = asa.ReferenceID WHERE a.ID > 0 AND asa.ID > 0

I do have where clauses for both tables on IDs which are Primary key for both tables.

Disabling the SQL_SAFE_UPDATES is not an option.

Edit: MySQL version is v5.6

Cid
  • 14,968
  • 4
  • 30
  • 45
Calin956
  • 25
  • 1
  • 7
  • https://stackoverflow.com/questions/21841353/mysql-delete-under-safe-mode Look at Peter B's answer – bjk116 Jan 21 '20 at 13:27
  • No that is not an option for me – Calin956 Jan 21 '20 at 13:32
  • It looks like you just want to do all ID's correct? And the > isn't working becuase I believe MySQL knows that. But you can't use the <> 0 trick? Do you have negative id's you are trying to protect? – bjk116 Jan 21 '20 at 13:33
  • 2
    [I can't reproduce](https://www.db-fiddle.com/f/qkA6BvQxLrCwMseSRm3JfX/0) – Cid Jan 21 '20 at 13:34
  • @bjk116 If I use '<>' or '!=' I get the same issue – Calin956 Jan 21 '20 at 13:39
  • 2
    @Cid I can reproduce it in your link also. Forgot to mention that I use v5.6 – Calin956 Jan 21 '20 at 13:41
  • 1
    Ok yea just confirmed things on my 5.6 system. An admittedly hacky non-nice work around is to select all the appropriate id you do want to update in a select query. Copy and past this to NotePad++ or something similar, then you can format it into a (id1, id2,....idn) format. Then change your update query to WHERE t.id IN (id1, id2, ...idn). Not ideal and definitely not what you want to do every time but if you're in a rush/need this now, it will work. – bjk116 Jan 21 '20 at 13:50

2 Answers2

0

Even though you are using the KEYS,I guess the KEYS are not properly used. MySQL documentation says.

If this variable is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably change or delete a large number of rows.

Your query below will affect same number of rows even with WHERE conditions. I suppose all the values of ID is more than 0 right? If so,then that is the reason for your error.

 UPDATE table1 a 
 INNER JOIN table2 asa 
 ON a.ID = asa.Table1Id 
 SET a.ReferenceID = asa.ReferenceID 
 --WHERE a.ID > 0 AND asa.ID > 0

One way is as suggested SET SQL_SAFE_UPDATES=0; (disable and try). Or try refining the WHERE condition. Also you can use LIMIT if it is a single table.

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
0

It seems that MySQL 5.6 is limited about executing an UPDATE statement along with a JOIN

So, instead of 

UPDATE table1 a
INNER JOIN table2 asa
ON a.ID = asa.Table1Id
SET a.ReferenceID = asa.ReferenceID
WHERE a.ID > 0 AND asa.ID > 0

You'll have to write as many queries as needed like :

UPDATE table1 a
SET a.ReferenceID = <The corresponding value in table 2>
WHERE a.ID = <The corresponding ID>

This being pretty annoying to type, one can use dynamic SQL to build the update queries :

SELECT CONCAT('UPDATE table1 a SET a.ReferenceID = ', asa.ReferenceID, ' WHERE a.ID = ', t.ID, ';')
FROM table1 t
INNER JOIN table2 asa
ON t.ID = asa.Table1Id;

In example :

Schema (MySQL v5.6)

CREATE TABLE test
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    foo VARCHAR(255)
);

CREATE TABLE test2
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    id_test INT NOT NULL,
    foo VARCHAR(255),
    FOREIGN KEY (id_test) REFERENCES test(id)
);

INSERT INTO test (foo) VALUES ('hello'), ('world');

INSERT INTO test2 (id_test, foo) VALUES (1, 'bar'), (2, 'baz');

Query #1

SELECT CONCAT('UPDATE test t SET t.foo = ''', t2.foo, ''' WHERE t.id = ', t.id, ';') AS 'sql query'
FROM test t
INNER JOIN test2 t2
ON t.id = t2.id_test;

This outputs :

UPDATE test t SET t.foo = 'bar' WHERE t.id = 1;
UPDATE test t SET t.foo = 'baz' WHERE t.id = 2;

The output can now be used to manually update the differents rows

View on DB Fiddle

Cid
  • 14,968
  • 4
  • 30
  • 45
  • It works, problem is that I have more than 1 million entries in the tables. And because of that I would not want to do it like this. – Calin956 Jan 21 '20 at 14:39
  • Can you write a python script? Sucks, but I'm not sure how many other options you have. You're trying to update all rows at once with a setting that prevents all rows at once from being updated. – bjk116 Jan 21 '20 at 19:39