33

Can the UPDATE query be used without a WHERE clause? And if so in what conditions?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
heretolearn
  • 6,387
  • 4
  • 30
  • 53
  • 1
    In oracle, you can omit the `where` clause => all rows in the table are updated. I don't know for the other ORDBMS but i think that this will update all the table rows too. – M. Abbas Aug 28 '12 at 15:55
  • Yes you can as has already been answered. That being said, I am of two minds on this. I've seen disasters happen when a `WHERE` clause is accidentally omitted and every record rather than just one is updated. While I'd love to see `UPDATE` without `WHERE` being treated as a syntax error, I'm also thinking that this is not the job of a programming language. – Bob Kaufman Dec 10 '18 at 16:31

3 Answers3

46

if you don't use the WHERE clause all the records on the table will be affected

Massimiliano Peluso
  • 26,379
  • 6
  • 61
  • 70
  • i still confuse. if we always have to state the where clause for each update on the same table, isnt this costs the server. why cant we just identify the row from last query and use it for next query? – stackunderflow Jul 29 '13 at 20:34
  • 6
    What if that's exactly what I want to happen? Do I need to add `WHERE 1=1` ? – ChristoKiwi Jan 22 '19 at 22:09
  • 5
    @ChristoKiwi PostgreSQL throws an error (`Unsafe query: 'Update' statement without 'where' updates all table rows at once`) in this case and does not make any changes. Looks like it's not allowed to do updates without a `WHERE` clause. However, your suggestion `WHERE 1=1` or any other trivially true statement (e.g. `WHERE id > 0`) seems to make the trick. – Patrick Feb 28 '19 at 15:36
  • 1
    I have been using SQL in many flavors for 25 years now, it's the first time I see this warning. I think it's good to see it, but I need a better way to avoid the trick "1=1" ... – Juan Salvador Jul 28 '19 at 16:44
  • you could use primary_key = primary_key instead of where 1=1. It makes more visual sense to me that way. – garyrgilbert Jan 29 '20 at 09:21
  • `1=1` or any other "trick" has the unvaluable advantage of making the intent very explicit, so yes, PostgreSQL is being very helpful. – Simone Jul 04 '23 at 10:07
3

So, I think when you want to update the whole field for some kind of reasons like updating the status of users enrollment to free for all users.

UPDATE users SET status = "free";
Muhammad Junaid
  • 31
  • 1
1

The UPDATE statement in SQL is used to update records in the table. We can modify one or multiple records (rows) in a table using UPDATE statement. If you do not use WHERE clause in UPDATE statement, all the records in the table will be updated.

Purushothaman Srikanth
  • 711
  • 4
  • 9
  • 22