Update Query Problem

[obrienkev]

Hi all,

The below query is causing me problems. I want to update certain fields only.
At the moment it sets all values in the ffnumber column in the details table to 0.

How do I fix this?

$query = "UPDATE details AS d, student AS s ".
                  "SET d.ffnumber = '$value' ".
                 "AND s.ffnumber = '$value' ".
                 "WHERE s.student = '$key' ";

When I print the query it seems to look fine.

Why is this?

Thanks,

Kevin.

[devinemke]

obrienkev wrote:

At the moment it sets all values in the ffnumber column in the details table to 0.

because you have not specified which records in the "details" table you wish to update in your WHERE clause. if you don't specify which records, your RDBMS will assume that you want to update all records.

[obrienkev]

Back again on this one...

What should I set my query to?

My tables are as follows:

details{course_ID, ffnumber, date_id}
student {student, ffnumber} - Primary Key: ffnumber

Thanks,

Kevin.

[Roger_Ramjet]

Another victim of mysql's sloppy syntax.

"INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table are joined to all rows in the second table)."

Make your join EXPLICIT and use a join condition to solve that problem.

That said: I see that you are trying to update the ffnumber in both tables. Difficult if you are going to do the join on them! 🆒

Are you sure you've got your database logic and schema thought through properly? Unusual to be updating a PK:FK pair at any time. IDs used as PK:FK are generally for internal database use and best created without any meaning to the outside world. That way they should not need updating, except when the wrong value is inserted as the FK by accident or error.