Nested Select in Update statement 2

[bateman23]

Hi,
my nested Select-Update-Statement doesn't work. I think it's because the SELECT-Statement returns an empty set.. is there a posibillity to run this statement if there is no empty set and insert NULL if it is? Or is there another error at all?

UPDATE proj_user_suche SET acronym = (SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"

Thanx in advance,
Daniel

 

[dalchri]

Perhaps this would work for you:

UPDATE proj_user_suche 
SET acronym = 
   IF((
      SELECT COUNT(*) 
      FROM acronym 
      LEFT OUTER JOIN link_acronym 
      ON acronym.id_acronym = link_acronym.id_acronym 
      WHERE projekte_nr="23") < 1,
      Null,
      (SELECT name 
      FROM acronym 
      LEFT OUTER JOIN link_acronym 
      ON acronym.id_acronym = link_acronym.id_acronym 
      WHERE projekte_nr="23"))

(SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"
[/code]

 

[bateman23]

mhh.. i just get SQL-Errors. So i tried to strip down the code, and now i'm with this small part.. and still get error:

SELECT IF((SELECT count(*) FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23" < 1),'NO','YES')

What am i doing wrong?

---------------------------------------
Visit me @:

http://www.daniel-purucker.de/

 

[swampBoogie]

What error do you get?

 

[bateman23]

Oh sorry for nit posting the error-message, but i thought it's the sort of standard-err. So here it is:

mysql> SELECT IF((SELECT count(*) FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym =
 link_acronym.id_acronym WHERE projekte_nr="23" < 1),'NO','YES');
ERROR 1064: 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 'SELECT count(*) FROM acronym LEFT OUTER JOIN link_
acronym ON ac

---------------------------------------
Visit me @:

http://www.daniel-purucker.de/

 

[swampBoogie]

You are using a version that does not support scalar subqueries. You need version 4.1

 

[bateman23]

:dohhh: Thanx, that's it. Upgrading to 4.1 did the job.

---------------------------------------
Visit me @:

http://www.daniel-purucker.de/