Correct way to perform an UPDATE on multiple tables?

Question

This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes using multiple queries). So, please hold my hand here, and tell me if this is being done the "right" way?:

Table Breakdowns

SettingsList

UserSettings

SettingTypes

Basically when I add a setting to the API (through the control panel) it gets put into SettingsList, I give the setting a value and a type (so that it actually does something) in the UserSettings table (this will be the only table accessed by the client from the app) . and the setting itself can only be a type specified in the enumeration (of sorts) in the SettingTypes table.

Right now, when I edit a setting from MY administrative control panel (the client will have far less control), I am running an update qiery that looks something like this:

UPDATE `SettingsList` l, `UserSettings` u
SET
l.settingName = 'pow',
l.settingDescription = 'otherTest',
u.setting = 0,
u.type = 1 WHERE
l.settingID = 3 AND u.settingID = 3

Does this look correct? Again, it does work but as most here would agree, just because it works, doesn't mean I did it right.

Well, I may have supplied more information than you need to give me an answer, but how am I doing in my first application with relational tables?

Answer 1

You can use JOIN clause, as mentioned by @tadman

UPDATE `SettingsList` l
JOIN `UserSettings` u
    ON l.settingID = u.settingID
SET
  l.settingName = 'pow',
  l.settingDescription = 'otherTest',
  u.setting = 0,
  u.type = 1
WHERE l.settingID = 3;

This is what you'd call a good programming etiquette. It is fine if you update as you currently are.

Answer 2

Usually updates to two tables are done as two different queries to avoid making a mistake and badly mangling both tables.

A combined update is a form of JOIN, so you need to be careful when establishing update conditions. I don't see how this query you've created is any better than two simple queries that achieve the same effect. If you need to make the operation atomic, frame it inside a transaction.

Answer 3

Use two queries inside a transaction.