MySQL 5.1.61 - Update table based on two seperate tables
I have three tables:
Table_1: id name 1 NULL 2 OLED 3 legion 4 project100 5 group3 6 0 7 25 Table_2: projectID externalID projectTypeID projectDescription 0 0 5 UNALLOCATED 25 220339 1 OLED Table_3: typeID typeDesc 1 Playbook Aligned 2 Transactional Project 3 External Programs 4 UPI 5 Unallocated
I am trying to update Table_1. I only want to update rows with a 'name' that is a digit. I know that I can select those by doing:
SELECT `name` FROM `Table_1` WHERE `name` REGEXP '^[0-9]*$'
This gives me:
name 0 25
What I want to do now is to update these Table_1 entries based on Table_2 and Table_3. I need to find the row in Table_2 where Table_2.projectID = Table_1.name. Then, I need to find the row in Table_3 where Table_3.typeID = Table_2.projectTypeID. Finally, I need to update Table_1.name with Table_3.typeDesc. It's a confusing situation - unfortunately I can't do much to change the way that these tables are set up. Any help is appreciated.
UPDATE Table_1 JOIN Table_2 on(Table_2.projectID = Table_1.name) JOIN Table_3 on(Table_3.typeID = Table_2.projectTypeID) SET Table_1.name = Table_3.typeDesc WHERE Table_1.name REGEXP '^[0-9]*$';