SQL Update across 3 tables - Where clause issue(?)

Question

We need to update various columns on 3 different tables via an input form. most of this is functional however when we try to update the other 2 tables that have been joined (publisher and category), it updates that record and every other record with the same input.

(for example if we change the genre from metal to jazz then all of the metal CD's will change to Jazz as well)

Below is the code we have so far for the update.

$sql = "UPDATE nmc_cd, nmc_category, nmc_publisher 
SET CDTitle ='$title', CDYear = '$year', nmc_publisher.pubID = '$publisherID', nmc_category.catID = '$categoryID', CDPrice = '$price', pubName ='$pubName', catDesc='$catDesc'
WHERE CDID = $id
AND nmc_category.catID = nmc_cd.catID
AND nmc_publisher.pubID = nmc_cd.pubID";

I am relatively new to this site so please if anything such as code, names of variables/functions etc. is needed please say and I will edit my post or reply.

Thanks in advance!

You can't do it in 1 statement, you have to split it into 1 update statement for each table you want to update. Best you create a transaction and commit it after all 3 updates. You could also write a stored proc and execute the statements in there (again within a transaction). — Igor, Mar 31 '16 at 15:14
Possible duplicate of [How to update two tables in one statement in SQL Server 2005?](http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005) — Igor, Mar 31 '16 at 15:16
This probably falls under the domain of DBMS specific quirks rather than being pure SQL. Please specify your target DBMS and we can be more concrete if this is possible in one query or not. Using pure SQL it's probably not. — apokryfos, Mar 31 '16 at 15:17

score 0 · Answer 1 · answered Mar 31 '16 at 16:01

A couple of recommendations.

1) qualify all column references in SQL statement that references more than one table, even when the column references aren't ambiguous to MySQL. (Also consider assigning a short alias to each table.) Several reasons for this, but one big one is that it lets a human reader know which table each referenced column is in.

2) ditch the old school comma operator for join operations, and use the JOIN keyword. Also move the join predicates from the WHERE clause to the appropriate ON clause.

3) for a multitable update, first write a SELECT statement, get that working and tested, and then convert that to an UPDATE statement

4) avoid SQL Injection vulnerabilities. The preferred pattern is to use prepared statements with bind placeholders. Or (less optimal) at a minimum, all potentially unsafe values that are included in the SQL text must be properly escaped.

Ignoring SQL Injection vulnerabilities (assuming that the contents of the variables have already been properly escaped)...

I would first write a SELECT statement that returns the current values of the columns we're planning to update, along with the new values we're planning to assign to those columns. For example:

 SELECT cd.cdtitle      AS old_cd_cdtitle
      , '$title'        AS new_cd_cdtitle

      , cd.cdyear       AS old_cdyear
      , '$year'         AS new_cdyear

      , pub.pubid       AS old_pub_pubid
      , '$publisherID'  AS new_pub_pubid

      , cat.catid       AS old_cat_catid
      , '$categoryID'   AS new_cat_catid

      , cd.cdprice      AS old_cd_cdprice
      , '$price'        AS new_cd_cdprice

      , pub.pubName     AS old_pub_pubname
      , '$pubName'      AS new_pub_pubname

      , cat.catDesc     AS old_cat_catdesc
      , '$catDesc'      AS new_cat_catdesc

   FROM nmc_cd cd
   JOIN nmc_category cat
     ON cat.catID = cd.catid
   JOIN nmc_publisher pub
     ON pub.pubID = cd.pubid
  WHERE cd.cdid = $id

(That is really just a guess, I'm not sure what you are actually trying to achieve.)

It seems really odd to assign a new value to the catid column, when that's referenced in a join predicate. To maintain the relationship between the rows in cd and cat, the catid in column in both tables would need to be updated, unless we're depending on an ON UPDATE CASCADE rule to propagate the change.

Without understanding what this statement is attempting to achieve, it's not possible to recommend any particular statement.

In terms of converting the SELECT into an UPDATE statement, replace the SELECT ... FROM with the keyword UPDATE.

And before the WHERE clause, add a SET statement. Taking the expresssions for old_cd_cdyear and new_cd_cdyear from the SELECT list, convert that into a SET clause like this:

    SET cd.cdyear       = '$year'

Subsequent assignments, use a comma in place of the SET keyword, e.g.

      , cd.cdprice      = '$price'

The "rules" for converting a SELECT statement to an UPDATE are addressed to MySQL. Other RDBMS have different syntax for multitable update. Absent a specification of what the statement is supposed to achieve (table contents both pre- and post- change, including rows that are not supposed to be changed), it's not really possible recommend appropriate syntax for it.) Without a specification, we're just throwing "try this" guesses at the wall to see what sticks. There's lots of things we can try, but without knowing what we're actually trying to achieve, how would we know if we "got it"? — spencer7593, Mar 31 '16 at 16:20
We have already joined the tables. All we are trying to do is have an update statement to update the information in the 3 tables when information is entered by the user on the website form. This is our join that we currently have `$sql = "SELECT * FROM nmc_cd INNER JOIN nmc_category ON nmc_category.catID = nmc_cd.catID INNER JOIN nmc_publisher ON nmc_publisher.pubID = nmc_cd.pubID WHERE CDID = $id ORDER BY CDTitle";` apologies for the formatting. — spesh, Mar 31 '16 at 16:28
@spesh: *Which* columns in *what* rows from *which* tables do you need to update? The specification "update information in the 3 tables" is vaguely imprecise. As I noted in my answer, if you have foreign keys that reference `cat.catid` with a ON UPDATE CASCADE, then when you assign a new value to `cat.catid`, those foreign key columns that reference the old value will also be changed. The relationship between the rows will be maintained. If you also assign a new value to `cat.catname`, then all of those same rows (that refer to `cat.catid`) are also going to reference the new `catname`. — spencer7593, Mar 31 '16 at 16:32
@spesh: I recommend that you figure out the individual update statements for each set of rows in the tables you need to update. Once you have those, you can work on combining those into a multi-table update. — spencer7593, Mar 31 '16 at 16:39

SQL Update across 3 tables - Where clause issue(?)

1 Answers1