How to update 3 tables simultaneously...
-
hello everyone..i have 3 tables t1,t2,t3..if i update t1,t2 and t3 must also be updated..how could i do this? i have just been tried [CODE]UPDATE t1,t2,t3 SET t1.f1='sample',t2.f1='sample',t3.f1='sample' WHERE t1.f1='test' and t2.f1='test' and t1.f1='test' [/CODE] using this,update fails if t2 and t3 could not find 'test' in their fields,if i use OR all records will be.. i have also tried JOINS [CODE] Update tbl_maincat AS m LEFT JOIN tbl_maincat AS m2 ON m.cTitle = m2.cTitle, tbl_main AS c LEFT JOIN tbl_main AS c2 ON c.MainCat = c2.Maincat, tbl_subcat AS s LEFT JOIN tbl_subcat AS s2 ON s.csCat = s2.csCat SET m.cTitle = 'Hardware25', m.cDesc = 'H2desc', c.MainCat = 'Hardware25', s.csCat = 'Hardware25' WHERE m.MainCatID=29 and c.Maincat='Hardware23' and s.csCat='Hardware23';[/CODE] same...because of the AND operator... if multiple query solved this..please let me know the proper construction in VB.net.. please everyone..its bugging me for almost a month.. im a newbie vb.net and mysql programmer.. thanks..
...i am dying to learn PLs and DBs...
-
hello everyone..i have 3 tables t1,t2,t3..if i update t1,t2 and t3 must also be updated..how could i do this? i have just been tried [CODE]UPDATE t1,t2,t3 SET t1.f1='sample',t2.f1='sample',t3.f1='sample' WHERE t1.f1='test' and t2.f1='test' and t1.f1='test' [/CODE] using this,update fails if t2 and t3 could not find 'test' in their fields,if i use OR all records will be.. i have also tried JOINS [CODE] Update tbl_maincat AS m LEFT JOIN tbl_maincat AS m2 ON m.cTitle = m2.cTitle, tbl_main AS c LEFT JOIN tbl_main AS c2 ON c.MainCat = c2.Maincat, tbl_subcat AS s LEFT JOIN tbl_subcat AS s2 ON s.csCat = s2.csCat SET m.cTitle = 'Hardware25', m.cDesc = 'H2desc', c.MainCat = 'Hardware25', s.csCat = 'Hardware25' WHERE m.MainCatID=29 and c.Maincat='Hardware23' and s.csCat='Hardware23';[/CODE] same...because of the AND operator... if multiple query solved this..please let me know the proper construction in VB.net.. please everyone..its bugging me for almost a month.. im a newbie vb.net and mysql programmer.. thanks..
...i am dying to learn PLs and DBs...
I'd be very reluctant to do this as a single query on a join. My preference would be to use a transaction on mutiple updates (Transactions and Atomic Operations).
-
I'd be very reluctant to do this as a single query on a join. My preference would be to use a transaction on mutiple updates (Transactions and Atomic Operations).
thank you sir,,but im a newbie mysql user..please give me a example of updating 3 tables..i have searched it thoroughly in google but all of their examples just resides in 2 tables....please?
-
thank you sir,,but im a newbie mysql user..please give me a example of updating 3 tables..i have searched it thoroughly in google but all of their examples just resides in 2 tables....please?
Reymelito If you have an example of an update on two tables I'd be interested in seeing it. Let me have the example or link and I'll have a proper pop at this. From http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html: >>In general, you cannot modify a table and select from the same table in a subquery Which rules out one option... My preferred appoach would still be transactions since it will be much more maintainable/readable. Here's an old thread on that http://www.codeguru.com/forum/showthread.php?t=371117&highlight=auto-commit Cheers Dave