Hi Folks
need a little help from any resident query gurus out there.
I have a main USER table, with various tables hanging off it.
Most tables will always have a corresponding record to USER, such as USER_FULL.
Other tables do not necessarily have a corresponding record, such as NOTES. Not all users have NOTES associated with them.
NOTES TABLE
id
userid
notes
I am building a user admin screen and I want to update various user data. I got this to work mostly, using an update with LEFT JOINs.
UPDATE users_full,
users
LEFT JOIN users_notes ON users_notes.userid = users.id
SET users_full.mdpass = '098f6bcd4621d373cade4e832627b4f6',
users_full.usertype = '9999',
users_full.pin = '1065',
users_notes.text = 'test text'
WHERE users_full.userid = users.id
AND users.id = '1'
Problem is of course, that I cannot join the USER table to the NOTES table for users that do not already have a corresponding record in NOTES.
I have found this syntax:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
I don't know if this is the only / best way to "insert where record doesn't exist" - however the next problem is that the majority of other tables DO have a corresponding record and therefore would need to be updated instead of inserted.
Can anyone tell me if it is possible to update and insert in a single query across multiple tables - and if so, some clues on how would be great :)
Many thanks
Ted
