How to update only month in date column?

Question

I have one table with 30 entries and date column is like following

2014-11-01
2014-11-02
2014-11-03
.
.
.
2014-11-30

Now i want to write MySQL query to update month from 11 to 10 or you can say from month November to October.

I want to change only month in all these dates from 11 to 10.

Answer 1

Use DATE_ADD function for changing the month and MONTH function for filtering the records. Assuming the table name is tbl and the column name is date, here's what the query will look like

UPDATE `tbl`
SET `date` = DATE_ADD(`date`, INTERVAL -1 MONTH)
WHERE MONTH(`date`) = 11