How to concate (Varchar and substract Year from date)?-CodePudding

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