homepage Welcome to WebmasterWorld Guest from 54.227.56.174
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
INSERT ON DUPLICATE UPDATE with LEFT JOINS
Need a little help with a mildly complex query
ManMountain




msg:4354777
 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

 

coopster




msg:4357213
 4:16 pm on Aug 31, 2011 (gmt 0)

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...]

Demaestro




msg:4357254
 5:19 pm on Aug 31, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved