Strange condition in Mysql query

Question

Basically I have to edit someone's else code and the query is:

DELETE  FROM some_table WHERE 1

Googling around didn't find anything similar.

I can't understand the condition. Would someone explain it to me please?

Surely they want to enforce the good practice of always adding a where clause. Very frecuently users accidentally delete ALL the table just to forget the condition. Some frontends give a warning to prevent this situation. — borjab, May 16 '14 at 14:13

score 1 · Answer 1 · answered May 16 '14 at 14:10

1

The WHERE 1 condition means that there's no condition. Delete everything from table. It is equivalent to DELETE FROM some_table. In other words, the condition says that "where true" (1 evaluates to true) so it means everywhere...

answered May 16 '14 at 14:10

marekful

14,986
6
37
59

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

This will delete all the rows from the table, because where 1 will be true for all the rows. It is like while(1) loop, which is always true.

Many times people put such default conditions at the top of a where condition as it enables them to freely chop and change the rest of the conditions. More details can be found in answers of this question.

edited May 23 '17 at 11:50

Community

1
1

answered May 16 '14 at 14:10

Saurabh

71,488
40
181
244

score 1 · Answer 3 · 2014-05-16T15:35:23.183

DELETE FROM some_table WHERE 1

is like

DELETE EVERYTHING FROM some_table

WHERE 1 will be true for all the rows as 1 equals true.

The database looks like this to Bob when it checks for the above

Mysql - "Hey Bob, can you DELETE some_table WHERE items equal 1?"
Bob - "Yes, a moment.."
Mysql - "Wa..wait..! What's 1, Bob?"
Bob - "Ah yeah, in my world 1 is everything that evaluates to True, 
          and that's the default state of a self-standing row, 
            but lemme check it for you.."

*Bob is checking the rows...*
row1 <-- 1

row2 <-- 1

row3 <-- 1

Bob - "So, I checked the table and everything in there is 1 (or True), 
           let's delete all some_table's content!"
Mysql - "Agreed."

score 1 · Accepted Answer · answered May 16 '14 at 14:32

1

As written, it would delete all the rows (because 1 is true). If you want to delete all rows, it is often more efficient to use truncate table:

truncate table some_table

One reason you might want to generate a statement with where 1 is so you can add additional clauses afterwards.

answered May 16 '14 at 14:32

Gordon Linoff

1,242,037
58
646
786

Though pretty much everyone was telling the same thing I am accepting this answer because it's exactly why it was used in my piece of code. – Euratus May 16 '14 at 14:41

score 0 · Answer 5 · answered May 16 '14 at 14:19

Normally you see such conditions (which always evaluates to TRUE in Mysql - other RDMS may give you "invalid relational operator" error) when query is built dynamically. For instance,

String basicQuery = "DELETE FROM table1 WHERE 1 " ; 
-- I'd use something less mysql specific, e.g WHERE (1=1) which works for every RDMS
if (some_condition) 
{
   basicQuery = basicQuery+ " AND field1 = :param1"; 
}
if (other_condition) 
{
   basicQuery = basicQuery+ " AND field2 = :param2"; 
} 
// etc

Strange condition in Mysql query

5 Answers5