Unanswered: How to Mimic Join "For Update" in MySQL 3.23.54?
I'm new here. So, if this question has been posted in the wrong forum, please tell me where to post it and I'll do so. If it doesn't belong on this site at all and you can suggest another place to ask it, I will be extremely grateful! Thanks.
Yeah, I know "For Update" isn't avail until MySQL version 4+ (I've read and studied the admin manuals enough to know that) and I'm definitely headed in that direction very soon... but the host for my dedicated server still has my server at v3.23.54 (although version 4 is present there, it's not yet running) and I really can't DO the upgrade until I've got all my existing MySQL enabled apps updated to the release level where they actually *need*, support and can take advantage of version 4. So, for the moment I'm *stuck* with 3.23.54 and that's what raises my question.
Here's my situation...
I'm working on a table conversion as part of the software upgrade process mentioned above.
I have two nearly identical tables which have a 1:1 relationship (i.e. every row in tablea has a matching row in tableb). and there IS of course, a matching key column in each table named linkid. So I know I can join them with no problem. (Incidentally, both tables are of the MYISAM variety.)
I "fat-fingered" an UPDATE query against tablea and accidentally destroyed the contents of a single column there (oops) . Fortunately the original (undamaged) data is intact and undamaged in tableb.
My goal is simple and obvious. Getting there doesn't seem to be. I need to join tablea to tableb and repair the contents of the damaged column in tablea by setting it to the undamaged value still stored in tableb.
The dilemma is that version 3.23.54 doesn't support the UPDATE syntax as part of a SELECT and the UPDATE statement doesn't support the JOIN syntax either.
I can write both select and update queries and have been doing that for years. But I haven't a clue how to work around the inherent limitations in this version
Can anyone offer an example (or point me to one) for how to achieve this "seemingly simple" goal?
1. Create a 3rd empty table with same format as other two...
2. Then, Use
INSERT INTO newtbl (col1, col2, col3) SELECT t1.col1, t1.col2, t2.col3 from damaged_table as t1, backup_table as t2 WHERE t1.col1=t2.col1;
to combine the individual columns from the JOINed view into the 3rd table.
INSERT INTO linknew (link_id, title, gallery) SELECT t1.link_id, t1.title, t2.gallery FROM link AS t1, linkbu AS t2 WHERE t1.link_id=t2.link_id;
When query is finished the once-empty 3rd table, 'linknew', contains data from both tables combined back into single rows but the data in the damaged column (gallery) has been replaced with the original undamaged data.
Hurray!! Thanks, guys. In this case, your silence for an hour while I studied the MySQL manual even MORE carefully was the biggest help of all!