Question 22152 in Mysql Question posted in
The official MySQL documentation can be found here.

How to concate (Varchar and substract Year from date)? – Mysql

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search