Mysql multiple table update/multiple delete

Portal Home > Knowledgebase > Articles Database > Mysql multiple table update/multiple delete

Posted by ilyash, 11-12-2008, 12:35 AM
Mysql multiple table update/multiple delete How would I do any of the above? Table structure: users --> t1: user_id, status cars --> t2: car_id, user_id, status car pictures --> t3: pic_id, user_id, status car picture comments --> t4: comment_id, pic_id, status So say I delete a user, or deactivate a user, I need to do a multiple update/delete this doesnt work (say for user_id 5): update t1, t2, t3, t4 set t1.status="DCT", t2.status="DCT", t3.status="DCT", t4.status="DCT" where t1.user_id ="5" AND t2.user_id = t1.user_id AND t3.user_id = t1.user_id AND t4.pic_id = t3.pic_id and the corresponding delete: delete from t1, t2, t3, t4 where t1.user_id ="5" AND t2.user_id = t1.user_id AND t3.user_id = t1.user_id AND t4.pic_id = t3.pic_id I was thinking to use join somehow but I'm unsure how Thanks for your help guys! __________________ webmaster A T 420th.com What is my Itunes playing? Visit 420th.com/nowplaying.jsp to find out. Ask the monkey: http://www.monkeyfaq.com
Posted by jaseeey, 11-12-2008, 01:15 AM
Instead of making all of the elements in the WHERE clause point back to t1's attribute, why not make them all equal 5? Also try replacing the AND operator with the OR operator. But, honestly, I would execute each one of these queries individually. It saves a lot of hassling around and makes it less vulnerable to error.
Posted by ilyash, 11-12-2008, 02:56 AM
Yea I tried that, does the same thing I think the error (no actual error, i mean like problem) occurs when I try to do AND t4.pic_id = t3.pic_id __________________ webmaster A T 420th.com What is my Itunes playing? Visit 420th.com/nowplaying.jsp to find out. Ask the monkey: http://www.monkeyfaq.com
Posted by coloheart, 11-18-2008, 12:26 PM
Why dont you use mysql trigger ?

Add to Favourites Print this Article