CODEWITHSUNDEEP
phpmysqlsqlsql-updatenested
Maurits Weebers
Maurits Weebers

Reputation: 69

php sql UPDATE with nested FROM (SELECT)

After hours of trying I need your advice.

I want to combine rows from 2 tables. After I created a new row in table1 I want to find a row in table2 and combine some of the fields.

If I put the nested SELECT in the SET function (SET postcode=(SELECT etc) is works, but if I put it in the FROM function is gives an Error that the syntax is wrong

my code:

$sql = "INSERT INTO instanties(institution, category, postcode) 
        VALUES('$emapData[0]', '$emapData[1]', '$emapData[2]')";

if ($conn->query($sql) === TRUE) {
    //get last added id
    $last = $conn->insert_id;

    //define WHERE function
    $where="postcode_id=$postcode_id AND (minnumber <= $number AND maxnumber >= $number)";

    //UPDATE last added row in table with info from other table
    $sql2 = "UPDATE instanties 
            SET postcode_id=pc.postcode_id  
            FROM 
            (
                   SELECT postcode_id 
                   FROM postcode 
                   WHERE $where LIMIT 1
            ) pc 
            WHERE id=$last";

            $result = $conn->query($sql2);
            if ($result) {
                echo 'update is done<br/><br/>';
            }
        }
        else {
            echo "Error: " . $sql2 . "<br>" . $conn->error.'<br/><br/>';
        }
    }
    else {
        echo "Error: " . $sql . "<br>" . $conn->error.'<br/><br/>';
    }

Upvotes: 1

Views: 767

Answers (2)

Naomi
Naomi

Reputation: 381

That's not a valid MySQL syntax. You cannot add a "FROM" clause to an UPDATE statement. http://dev.mysql.com/doc/refman/5.0/en/update.html

However, what you want to accomplish is still possible this way:

$sql2 = "UPDATE instanties 
        SET postcode_id=
        (
               SELECT postcode_id 
               FROM postcode 
               WHERE $where LIMIT 1
        ) 
        WHERE id=$last";

As long as there is only 1 result from the nested SELECT (and your LIMIT 1 kinda does that).

EDIT: If you need many fields from the postcode table, you can join on it:

$sql2 = "UPDATE instanties as i
        JOIN (
            SELECT * 
            FROM postcode 
            WHERE $where LIMIT 1
        ) as pc
        SET i.postcode_id=pc.postcode_id
        WHERE i.id=$last";

We would usually use an "ON" clause with the join, but since you're only updating 1 row and your nested SELECT will also only return 1 row, it's not necessary.

Upvotes: 1

jmonrio
jmonrio

Reputation: 81

try this:

$sql2 = "UPDATE instanties 
        SET postcode_id=(
               SELECT postcode_id 
               FROM postcode 
               WHERE $where LIMIT 1) 
        WHERE id=$last";

Upvotes: 0

Related Questions