Welcome to WebmasterWorld Guest from 54.163.168.15

Forum Moderators: coopster & jatar k

INSERT ON DUPLICATE UPDATE with LEFT JOINS

Need a little help with a mildly complex query

   
7:03 am on Aug 24, 2011 (gmt 0)



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
4:16 pm on Aug 31, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Hi Ted and welcome to WebmasterWorld.

Have you considered a stored procedure? You can read more about transactional statements in the MySQL docs:
[dev.mysql.com...]
5:19 pm on Aug 31, 2011 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



ManMoutain,

Making use of a trigger may also make sense if I understand you correctly.

[dev.mysql.com...]

You can define a BEFORE UPDATE trigger on the table then do your logic there.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month