Question 289794 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql update error: 'Update' statement without 'where' updates all table rows at once

I want to run this query:

UPDATE users as u
INNER JOIN (SELECT MIN(updated_at) as start, h.uuid
       From users_history as h
       where h.promoted = true) new on new.uuid = u.uuid
 SET u.start_time = new.start;

And I’ll get the following error:

Unsafe query: 'Update' statement without 'where' updates all table rows at once

How should I rewrite this query to fix this problem?
I’m trying to set start_time on the user table to the first time in the user_history, the user got promoted.

Tags:

2

Answers


  1. 0

    The message is informing you that your query is "unsafe" (by some standard of safety) because it does not include a WHERE clause.

    It is issued because some MySQL feature known as "safe updates" has been enabled on your database.

    In other words, MySQL is postulating that you intended to include a WHERE clause, but you forgot to include it, so this query as it stands is about to destroy your database by modifying every single record, so MySQL is preventing you from doing this in an attempt to save you from yourself. (In doing so, MySQL is also postulating that you are working on the original instance of a database, that the database is very important for your operations, and that you have been taking no backups. But I digress.)

    • One way to work around this problem is to disable that feature on your database. This depends on how your database has been configured, and you might not even have the necessary privileges to do so.

    • Another way to work around this is to use the following construct:

    SET SQL_SAFE_UPDATES = 0;
(your query here)
SET SQL_SAFE_UPDATES = 1;
    • The best way to work around this is to add a WHERE clause that selects everything. The way to do this varies from database system to database system; some will accept the very reasonable WHERE TRUE clause; others require something more complicated, like WHERE 1=1. The point is, this is a WHERE clause that simply selects everything, just as the absence of a WHERE clause would select everything. By including a WHERE clause we are telling MySQL that we know what we are doing and it does not need to save us from ourselves.
    Login or Signup to reply.
  2. 0

    Add where codition as WHERE new.uuid = u.uuid as follows

    UPDATE users as u
INNER JOIN (SELECT MIN(updated_at) as start, h.uuid
       From users_history as h
       where h.promoted = true) new on new.uuid = u.uuid
 SET u.start_time = new.start
 WHERE new.uuid = u.uuid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search