Is It Possible Query Data That Are Not Equal to the Specified Condition

SQL WHERE condition is not equal to?

You can do like this

DELETE FROM table WHERE id NOT IN ( 2 )

OR

DELETE FROM table WHERE id <>  2 

As @Frank Schmitt noted, you might want to be careful about the NULL values too. If you want to delete everything which is not 2(including the NULLs) then add OR id IS NULL to the WHERE clause.

Equal, Not equal conditions on same field in single sql query

In MySQL, you can write this very cleanly:

select sum(email <> "") not_blank, sum(email = "") blank from pages;

It uses the fact that true evaluates to 1 and false evaluates to 0 in MySQL.

In other DBMSes, equivalent query will be:

select sum(case when email <> "" then 1 else 0 end) not_blank,
    sum(case when email = "" then 1 else 0 end) blank
from pages;

If you want to use COUNT:

select count(case when email <> "" then 1 end) not_blank,
    count(case when email = "" then 1 end) blank
from pages;

query not equal doesn't work

SELECT * FROM all_conversations WHERE deleted_1 <> 1 OR deleted_1 IS NULL

NULL values need special treatment: http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html

I'd suggest using the diamond operator (<>) in favor of != as the first one is valid SQL and the second one is a MySQL addition.

Select from a table where fields don't match conditions

The key is the sql query, which you will set up as a string:

$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";

Note that there are a lot of ways to specify NOT. Another one that works just as well is:

$sqlquery = "SELECT field1, field2 FROM table WHERE columnA != 'x' AND columbB != 'y'";

Here is a full example of how to use it:

$link = mysql_connect($dbHost,$dbUser,$dbPass) or die("Unable to connect to database");
mysql_select_db("$dbName") or die("Unable to select database $dbName");
$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";
$result=mysql_query($sqlquery);

while ($row = mysql_fetch_assoc($result) {
//do stuff
}

You can do whatever you would like within the above while loop. Access each field of the table as an element of the $row array which means that $row['field1'] will give you the value for field1 on the current row, and $row['field2'] will give you the value for field2.

Note that if the column(s) could have NULL values, those will not be found using either of the above syntaxes. You will need to add clauses to include NULL values:

$sqlquery = "SELECT field1, field2 FROM table WHERE (NOT columnA = 'x' OR columnA IS NULL) AND (NOT columbB = 'y' OR columnB IS NULL)";

SQL Statement - Select Where * Not equal to

If you want to include NULL values as "not equal" then you need a NULL safe operator. Otherwise, the <> returns NULL, which is treated as "false" in a WHERE clause.

You haven't specified your database, but standard SQL provides one:

where Drive_Status is distinct from 'Success'

If Drive_Status is NULL, then this returns TRUE rather than NULL.

Not all databases support this. Some use <=> as NULL-safe equality (that is NULL <=> NULL evaluates to true). In these, you can use:

where not Drive_Status <=> 'Success'

And in others, you need to be more specific:

where Drive_Status <> 'Success' or Drive_Status is null

How to update data in Mysql using not equal condition

Use AND instead of OR if you want to update all records except Ceri and Van

UPDATE property_list SET agent = "Property Agent" WHERE agent != "Ceri" AND agent != "Van";

---

Related Topics