CODEWITHSUNDEEP
mysql
Tower
Tower

Reputation: 1287

Update table based on Month from another table

I have these two tables in a MySQL database that are dependent on each other. Currently the DateExpires field in Table 2 needs to be a date formatted as yyyy-mm-dd h:m:s But I’m trying to get it to be one year and on the first day from the DateJoined field or the Renewal Month field in Table1, like: 2014-03-01 12:00:00

I’ve been trying to Update them based on the Renewal Month and the DateJoined field but nothing gives me the result im looking for, how can I do this?

enter image description here

Upvotes: 2

Views: 202

Answers (3)

Steven Moseley
Steven Moseley

Reputation: 16355

Here you go. You're going to do an update, joining the two tables (1 and 2) along with a derived table of month names to map to a month number. You're setting t2.DateExpires to a concatenation of values from the join.

Here's a demo of it working: http://sqlfiddle.com/#!2/a388d/1

UPDATE `Table1` AS `t1`
    INNER JOIN `Table2` AS `t2` 
        ON `t2`.`IDMember` = `t1`.`ID`
    INNER JOIN (
        SELECT '01' AS `number`, 'Jan' AS `name`
        UNION SELECT '02', 'Feb'
        UNION SELECT '03', 'Mar'
        UNION SELECT '04', 'Apr'
        UNION SELECT '05', 'May'
        UNION SELECT '06', 'Jun'
        UNION SELECT '07', 'Jul'
        UNION SELECT '08', 'Aug'
        UNION SELECT '09', 'Sep'
        UNION SELECT '10', 'Oct'
        UNION SELECT '11', 'Nov'
        UNION SELECT '12', 'Dec'
    ) AS `m` ON `m`.`name` = `t1`.`RenewalMonth`
SET `t2`.`DateExpires` = CONCAT(
                    IF(YEAR(`t1`.`DateJoined`), YEAR(`t1`.`DateJoined`), YEAR(NOW())) + 1, 
                    '-', `m`.`number`, '-01 00:00:00')

Upvotes: 1

PinnyM
PinnyM

Reputation: 35531

Use DATE_ADD to add a year, and DATE_FORMAT to convert to the desired format:

UPDATE Table2
SET DateExpires = DATE_FORMAT(DATE_ADD(t1.DateJoined, 'INTERVAL 1 YEAR'), '%Y-%m-%d %h:%i:%s') 
FROM Table1 t1
WHERE IDMember = t1.ID

If you want to base this on RenewalMonth, you can do it like this:

UPDATE Table2
SET DateExpires = DATE_FORMAT(DATE_ADD(STR_TO_DATE(
   concat(t1.RenewalMonth, ' ', 
          DAY(t1.DateJoined), ', ',
          YEAR(t1.DateJoined)), '%M %d,%Y'), 
   'INTERVAL 1 YEAR'), '%Y-%m-%d %h:%i:%s') 
FROM Table1 t1
WHERE IDMember = t1.ID

Upvotes: 1

skparwal
skparwal

Reputation: 1084

Try this:

update table2 as t2 
inner join table1 as t1 on t2.IDMember = t1.ID 
set DateExpires = date_format(date_add(t1.DateJoined, 'interval 1 year'), '%Y-%m-%d 12:00:00')

Upvotes: 0

Related Questions