SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Malaysia
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with UPDATE/Nested SELECTs

    hi guys...

    I'm trying to get this piece of code working, and I can't seem to find out what's wrong with it!

    PHP Code:
    UPDATE PDC_EMP
    SET FOOD_ID
    =(SELECT FOOD_ID FROM FOCUSNIGHT WHERE ITEM='Jell-O')
    WHERE WW_ID=10662106
    Basically, in words: I have 2 tables, PDC_EMP and FOCUSNIGHT, where FOOD_ID is the "common key" to both. What I want to do is to set the FOOD_ID in PDC_EMP, where the WW_ID is a column in PDC_EMP and is 10662106.

    SQL gives me an "you have an error in SQL syntax", and it indicates Line 2.

    Am I doing something wrong here? Perhaps my nested SELECT statement can't be used?

    Thanks for the help!
    Last edited by infinitium; Jul 18, 2001 at 02:19.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using a RDBMS that impliments standard ANSI SQL92 or some hack job such as MySQL?

    If you are using MySQL, MySQL is not a true RDBMS. It is a RDBMS like database but it does not fully impliment a relational data model. Thus things such as nested selects are not supported.

    If it looks like a duck, walks like a duck and quacks like a duck, then it probably is a duck. MySQL looks like a RDBMS, walks like a RDBMS but it doesn't quack like a RDMS
    Last edited by freakysid; Jul 18, 2001 at 03:12.

  3. #3
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Malaysia
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe.. I'd go for the "hack job like mySQL"..... oh man! Don't tell me mySQL doesn't support such relatively "simple" nested queries!

    I always thought mySQL supported the ANSI standard though a couple of functions didn't run on it....

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if my post is a little speckled with emotive language. But I am at the www.mysql.com site now, reading through the manual. This got by blood pressure up http://www.mysql.com/doc/B/r/Broken_Foreign_KEY.html MySQL must have hired someone from the Microsoft School of Propaganda to write that page. It's one thing to admit that there is a feature that is not supported in your software. But to turn things on their head and try and palm of a weakness in your software as a feature - that takes the cake.

    Seriously consider using PostgreSQL. Unless you are tweeking away at a dedicated db server with 1+GIG of RAM on a quad PIII 1GIG system, then performance issues are not going to be your major concern. I will be using PostgreSQL over MySQL for everyday web development in future because I can't stand not using a propper RDMS any longer.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    :shudder: that is just.. :sigh:

  6. #6
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Last I heard, MySQL doens't support nested queries. Someone please tell me this has changed.

  7. #7
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Malaysia
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WTF???!!!

    Oh CRAP!! I don't believe this....! What good is an SQL language if it can't even support nested SELECTs? Even a "primitive" DB like one I used last year (aargh.. can't remember what it's called) could support multiple nested subqueries.

    Sigh. Anyway, here's what I did... amazing how ideas come to you in a fit of rage

    I managed to overcome the nested SELECT problem by storing the result of the nested query in a temp. variable, and then plugging that into the outer loop:

    PHP Code:
    if ($submit) {
                echo (
    "<BR>**********The food you selected are:**********<BR>");
                                
                    foreach(
    $foodtype as $key => $value){
                        
    $sqlselect = ("SELECT FOOD_ID FROM FOCUSNIGHT WHERE ITEM='$value'"); 
                        
    $result mysql_query($sqlselect);    // run the query thru SQL
                        
    $row mysql_fetch_array($result);    // get location of result in array
                        
    echo $sqlselect;
                        echo 
    "<BR>";
                        echo 
    $row[0];    // print out result 
                        
    echo "<BR>";
                        
                        
    $sqlselect1 = ("UPDATE PDC_EMP SET FOOD_ID='$row[0]' WHERE WWID='$pid_own'"); 
                        echo 
    $sqlselect1;
                        echo 
    "<BR>";
                    } 
    I must admit it's terribly unefficient, but I can't think of anything else... for now (except to upgrade to PostgreSQL, FreakySid

    Anyway... appreciate the input.. thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •