Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

Rogelio Valdez
Rogelio Valdez
6,244 Points
Posted by 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
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
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
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
Rogelio Valdez
Rogelio Valdez
6,244 Points

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