[SOLVED] Syntax Issue (Probably Easy)

[hoopplaya4]

I'm having some difficulty with the Syntax on this statement:

 

<?php 
$data = mysql_query("UPDATE profilePics SET def='0' WHERE filename= (SELECT filename FROM profilePics WHERE usrID='$usrID' AND def='1'")) or die(mysql_error());

 

I get the following error:

 

Parse error: syntax error, unexpected ')'

 

What am I doing wrong here?  Thanks!

[ober]

$data = mysql_query("UPDATE profilePics SET def='0' WHERE filename= (SELECT filename FROM profilePics WHERE usrID='$usrID' AND def='1'")) or die(mysql_error();

All you have to do is count.  The "or die" part never gets passed to mysql_query().

[hoopplaya4]

Thanks for the reply Ober, I tried your example verbatim, and I'm still getting the same syntax error.  So this time, I tried removing the or die altogether:

 

<?php
$data = mysql_query("UPDATE profilePics SET def='0' WHERE filename= (SELECT filename FROM profilePics WHERE usrID='$usrID' AND def='1'"));

And with this one, I am still getting the same error as well.  :-\

 

[ober]

What version of MySQL?  MySQL 4 doesn't support subqueries, which is what you are trying to do.

[mrMarcus]

<?php
$data = mysql_query("UPDATE profilePics SET def='0' WHERE filename= (SELECT filename FROM profilePics WHERE usrID='$usrID' AND def='1')");

move your double-quote outside the inside bracket ')");

[ober]

Damn... missed that.  Good catch.

[mrMarcus]

^gracias amigo.

 

should work now.

[hoopplaya4]

Thanks for the help mrMarcus.  This has certainly cured my syntax issue!!

 

But, I'm now getting the following MySQL error:

 

You can't specify target table 'profilePics' for update in FROM clause

 

I'm not sure what this means off-hand.  If anyone does, that'd be great.  Otherwise, I'm gonna see what I can find.  Thanks again!

[.josh]

basically it means you can't do a subquery selecting data from the same table you are updating.  To get around it, you have to create a temp table and/or temp vars.  I don't personally know how to write that for you; that's beyond my sql knowledge.  I'll move this to the sql forum where you'll get a more specific answer.

[hoopplaya4]

Got it figured out!  Here's the statement I used:

 

UPDATE profilePics SET def = '0' WHERE filename = ( SELECT filename
FROM (

SELECT *
FROM profilePics
) AS temp
WHERE usrID = '25'
AND def = '1' ) 

 

Thanks everyone.