not exist syntax?

[thefollower]

I can't see where I am going wrong but i get same error with both attempts ... how is the syntax suppose to be on a NOT EXISTS function in mysql??

 

I tried these two:

 

$UPDATE = mysql_query("UPDATE userregistration SET C = 0 WHERE C < 0 AND ActivateCode = '' NOT EXISTS (SELECT UserID FROM userbans WHERE BanType='Jailed')")
Or die(mysql_error());

 

And

 

$UPDATE = mysql_query("UPDATE userregistration SET C = 0 WHERE C < 0 AND ActivateCode = '' AND NOT EXISTS (SELECT UserID FROM userbans WHERE BanType='Jailed')")
Or die(mysql_error());

 

Both gave this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT UserID FROM userbans WHERE BanType='Jailed')' at line 1

[rhodesa]

if i remember right, it should be like so:

 

$UPDATE = mysql_query("UPDATE userregistration SET C = 0 WHERE C < 0 AND ActivateCode = '' AND UserID NOT IN (SELECT UserID FROM userbans WHERE BanType='Jailed')")
   Or die(mysql_error());

 

...that is assuming your UserID column in userregistration is called UserID

[fenway]

And LEFT JOIN ... IS NULL would be better, but your syntax is correct.

[rhodesa]

i know of SELECT...LEFT JOIN...IS NULL, but was unsure if you could do it in an UPDATE

[thefollower]

why would it be "better", whats the main differences?

[fenway]

Check the EXPLAIN of each and you'll see.

[rhodesa]

LEFT JOIN...IS NULL is faster then nested SELECT statements

 

fenway, how would the syntax go for doing an UPDATE...LEFT JOIN...IS NULL?