Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Development Tools Database Foundations Joining Relational Data Between Tables in SQL Keys and Auto-Incrementing Values

Rogelio Valdez
Rogelio Valdez
6,244 Points

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

What's the best way to get around this error? (Without disabling safe mode).

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.

I get the error when running this code:

UPDATE movies SET genre_id = 2 WHERE id = 8 OR 9;

I thought this updates a value through their primary key (ID) and there should be no problem.

The only way I found to get around this is modifying one row at a time.

UPDATE movies SET genre_id = 1 WHERE id = 8;
UPDATE movies SET genre_id = 1 WHERE id = 9;

I saw something in stack overflow about using this format.

UPDATE schemaname.tablename SET columnname=1;

but I couldn't get it to work.

Anyone knows another way?

1 Answer

Vedran Brnjetić
Vedran Brnjetić
6,004 Points

Hi Rogelio,

First off, you were using the OR operator in a wrong way.

OR separates your WHERE clause into two logical parts, I'll put them in brackets so you can understand better what it does.

WHERE (id = 8) OR (9);

So the 9 triggers the 1175 error, since it is not a key, it is just a constant.

To make it work correctly, you should write like this

UPDATE movies SET genre_id = 2 WHERE id = 8 OR id = 9;

But that is a long and inefficient way of doing it because you have to write "OR id = " for every ID you want to match.

http://www.w3schools.com/sql/sql_and_or.asp

There is a better way to do it. The IN operator. http://www.w3schools.com/sql/sql_in.asp

Like this:

UPDATE movies SET genre_id = 2 WHERE id IN (8, 9);
Rogelio Valdez
Rogelio Valdez
6,244 Points

I just tried it and this and it works!! This does it in one step instead of two (as I did it before). Thank you!

Vedran Brnjetić
Vedran Brnjetić
6,004 Points

You are welcome and happy learning.

If I solved your problem, would you be kind and mark my answer as best? That would mean so much to me. All best.

Rogelio Valdez
Rogelio Valdez
6,244 Points

Of course, I was so excited that I forgot. There you go.