My Question is "delete from table where 1=1" . Why it had came into picture in sql server

Question

0.00/5 (No votes)

See more:

I attended some interviews in that they asked these questions.

what is result of execute this command "delete from table where 1=1"

my ans is

delete from table where 1=1

and

delete from table

both are doing same functionality. again they asked then why we include where 1=1? is there any special reason?

and another one is instead of 1=1 can we use other numbers like 2=2 / 3=3 /1=2/2=1 etc..

please verify i have lot of confusion in that. i could not provide perfect ans on this to interviewer..

Thanking you,

Posted 12-Aug-13 23:43pm

DGKumar

Updated 12-Aug-13 23:44pm

v2

Add a Solution

Comments

[no name] 13-Aug-13 5:46am

http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-08-12T23:56:00

ThePhantomUpvoter has provided you with a good link and it answers your original question, but I'd like to add a couple of things: Don't do it.

The WHERE 1=1 acts as placeholder for adding AND userName='FRED' and so forth conditions where you aren't sure exactly what will be added. As such you could replace it with any other condition which evaluates to true without affecting the outcome. But...it's a dangerous practice, and not one I would recommend. What happens if the additional clauses are not added, because the user doesn't enter any? Particularly with a DELETE command, this is spectacularly poor design because the default action is to delete everything in the database table...ouch!

A better approach is to construct the whole WHERE clause intelligently, specifically checking if there is anything to add at all, and not performing the command at all if not. Only if there is more than one item, do you need to faff with AND operators.

You could replace it with WHERE 1=2 instead, but only if you them replace the AND clause with OR - which may badly affect the delete list that is generated so care should be taken to ensure the user realises exactly what he is deleting!