Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 enter image description here

UserSettings enter image description here

SettingTypes enter image description here

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?

share|improve this question

migrated from stackoverflow.com Apr 13 '13 at 9:20

This question came from our site for professional and enthusiast programmers.

    
Had no idea that even existed. Can I move this? –  anwyatt Apr 12 '13 at 22:26
1  
It'll automatically be moved. You can flag it yourself, and ask for moderator attention. –  hjpotter92 Apr 12 '13 at 22:26
    
Flagged, thank you! –  anwyatt Apr 12 '13 at 22:28

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Use two queries inside a transaction.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.