CODEWITHSUNDEEP
mysqlsqldatesql-update
thecore7
thecore7

Reputation: 484

Mysql updating date field with 1 year

I have this query:

update prices_types_company1 set 'date'=DATE_ADD('date',INTERVAL 1 year)

which I am trying to execute directly in phpMyadmin to increase all date fields with 1 year but it returns error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''date'=DATE_ADD('date',INTERVAL 1 year)' at line 1

what is wrong with it and what other query I can execute to increase the date with 1 year. Field "date" is type date..

Thank you

Upvotes: 0

Views: 2041

Answers (3)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

MySQL behaves rather weird in a number of situations, update is one of them. You will have to do something like:

update prices_types_company1 
    set date=DATE_ADD(date,INTERVAL 1 year)
order by date desc;

to avoid duplicate key error. Example:

create table t (d date not null primary key);
insert into t (d) values ('2014-06-05 12:00:00'),('2014-06-06 12:00:00');
update t set d = DATE_ADD(d, interval 1 day);
    ERROR 1062 (23000): Duplicate entry '2014-06-06' for key 'PRIMARY'
update t set d = DATE_ADD(d, interval 1 day) order by d desc;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

Remove single quotes and use backticks like this

UPDATE prices_types_company1 SET `date`=DATE_ADD(`date`,INTERVAL 1 YEAR);

Upvotes: 2

Jens
Jens

Reputation: 69440

remove all single qoutes:

update prices_types_company1 set date=DATE_ADD(date,INTERVAL 1 year)

Upvotes: 1

Related Questions