Mysql Update columns from same table columns

MySQLupdate

I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment

I have added 2 columns in this table named service_start_date and service_end_date. I want to update these columns. service_start_date will be the first payment date and service_end_date will be payment_due_date+period_months. Then next service_start_date will be (payment_due_date+period_months)+1 day and its service_end_date will be service_start_date+period_month and so on for the next payments.

here is the queries to implement

CREATE TABLE `temp_table` (
    `invoice_id` int(11) DEFAULT NULL,
    `payment_id` int(11) NOT NULL DEFAULT '0',
    `payment_due_date` datetime DEFAULT NULL,
    `period_months` varchar(255) DEFAULT NULL,
    `service_start_date` date DEFAULT NULL,
    `service_end_date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

insert into `temp_table`
    (`invoice_id`,     `payment_id`,     `payment_due_date`,     `period_months`,     `service_start_date`,     `service_end_date`)
values 
('2065','3337','2015-12-15 00:00:00','3',null,null),  -- '' is not a valid date
('2065','3338','2016-03-14 00:00:00','3',null,null),
('2065','3339','2016-06-12 00:00:00','3',null,null),
('2065','3340','2016-09-10 00:00:00','3',null,null);

select * from temp_table returns this:

enter image description here

What I want is this:

enter image description here

Note: There are multiple invoices in this table and there are multiple payments for each table. I need this to be solve in one single query.

If you have further questions and queries, then please comment.

How can I do this?

Best Answer

I think that you want something like the following:

BEFORE:

('' is not a valid date - used null instead)

mysql> select * from temp_table;
+------------+------------+---------------------+---------------+--------------------+------------------+
| invoice_id | payment_id | payment_due_date    | period_months | service_start_date | service_end_date |
+------------+------------+---------------------+---------------+--------------------+------------------+
|       2065 |       3337 | 2015-12-15 00:00:00 | 3             | NULL               | NULL             |
|       2065 |       3338 | 2016-03-14 00:00:00 | 3             | NULL               | NULL             |
|       2065 |       3339 | 2016-06-12 00:00:00 | 3             | NULL               | NULL             |
|       2065 |       3340 | 2016-09-10 00:00:00 | 3             | NULL               | NULL             |
+------------+------------+---------------------+---------------+--------------------+------------------+
4 rows in set (0.00 sec)
mysql>

UPDATE QUERY:

UPDATE temp_table
SET 
service_start_date = date(payment_due_date), 
service_end_date = date(payment_due_date) + interval 3 month;
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4  Changed: 4  Warnings: 0

And the result:

(I'm assuming that the service end_date is always 3 months after the payment_due_date).

mysql> select * from temp_table;
+------------+------------+---------------------+---------------+--------------------+------------------+
| invoice_id | payment_id | payment_due_date    | period_months | service_start_date | service_end_date |
+------------+------------+---------------------+---------------+--------------------+------------------+
|       2065 |       3337 | 2015-12-15 00:00:00 | 3             | 2015-12-15         | 2016-03-15       |
|       2065 |       3338 | 2016-03-14 00:00:00 | 3             | 2016-03-14         | 2016-06-14       |
|       2065 |       3339 | 2016-06-12 00:00:00 | 3             | 2016-06-12         | 2016-09-12       |
|       2065 |       3340 | 2016-09-10 00:00:00 | 3             | 2016-09-10         | 2016-12-10       |
+------------+------------+---------------------+---------------+--------------------+------------------+
4 rows in set (0.01 sec)

mysql>

[EDIT - in response to the OP's comments]

We need clearer explanations - clear input data, clear output data and (perhaps most importantly) the logic you use to get your derived data.

If you have complicated logic involved in the updates, you might consider using TRIGGERs - these can have a complex programming logic behind them. It is always better to perform data manipulation operations as close as possible to the data and as soon as possilbe - apart from direct SQL, nothing is closer or can be performed sooner.

You might also like to consider GENERATED colums (also known as COMPUTED and/or CALCULATED colums/fields - depending on your RDBMS). These can be VIRTUAL (i.e. calculated on the fly) or STORED (i.e. actually written to disk) - again, better than code external to the database and faster and more reliable.

The point is, if it's done in and controlled by the database server, all applications touching the database will work correctly for that particular field/values - whereas if you implement logic in your application, every application that touches that database will have to re-implement it (and require testing/debugging again and again). Since data tends to be far longer lived than IDEs/app development frameworks, it is vastly preferable to keep logic in the RDBMS rather than in developers' heads!

Related Question