I am trying to update a column in the mainDB table, based on data in the engine table. The date in the main DB table is stored in (YYYY-MM_DD) format and I want to subtract only the year
I'm not sure how to properly join these two tables to update the percentage
UPDATE maindb
JOIN engine ON engine.COL_3 = CONCAT(maindb.COL_1,"-", SUBSTRING_INDEX(maindb.COL_2,"-",-1))
SET Maindb.COL_3 = engine.COL_4,
CodePudding user response:
Your join condition is off. I think you want:
UPDATE maindb m
INNER JOIN engine e
ON RIGHT(e.COL_3, 4) = LEFT(m.COL_2, 4)
SET m.COL_3 = e.COL_4;
I suspect that m.COL_2
is text, in which case the above should work. It should be a date, in which case we should extract the year and then cast to text before comparing to the the other table.
CodePudding user response:
What is the data types of the fields?
There are different solutions for different data types
If all fields is varchar
UPDATE maindb m
JOIN engine e ON e.COL_1 = m.COL_1
AND e.COL_2 = LEFT(m.COL_2, 4)
SET m.COL_3 = e.COL_4
If maindb.COL_2 is date
UPDATE maindb m
JOIN engine e ON e.COL_1 = m.COL_1
AND e.COL_2 = YEAR(m.COL_2)
SET m.COL_3 = e.COL_4