Home > Software design >  How to concate (Varchar and substract Year from date)?
How to concate (Varchar and substract Year from date)?

Time:11-21

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,                                    

enter image description here

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
  • Related