Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
arunkar
Forum Commoner
Posts: 50 Joined: Mon Feb 25, 2008 10:37 pm
Post
by arunkar » Wed Oct 08, 2008 4:32 am
Hi Guys,
I'm using MySQL 4.1.20
I need to add expiry date to the users table. The users table already has the joindate. The expiry date is 1 year from joint date. So I did the below SQL , which gives me error
Code: Select all
//registerDate datetime
//expiryDate datetime
Update jos_users set expiryDate= ADDDATE('Select registerDate from jos_users', INTERVAL 1 YEAR);
// OR
Update jos_users set expiryDate= DATE_ADD('Select registerDate from jos_users', INTERVAL 365 DAY);
// both the above SQL statements give me this optput...
(0 row(s)affected,372 warning(s))
(0 ms taken)
Any suggestions guys to add one year by using the existing datetime in the table.
Thanks
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 08, 2008 5:04 am
First, it's not the right way to use a subselect - where did these quotes come from?
Second, you don't need to use a subselect, because it's just another field in the same row.
Lets have this table:
mytable
and the query:
[sql]UPDATE mytable SET value2 = value2 + value1 + 1[/sql]
Then the result:
Code: Select all
value1 | value2
0 | 2
2 | 6
4 | 10
There are 10 types of people in this world, those who understand binary and those who don't
adroit
Forum Commoner
Posts: 37 Joined: Fri Aug 08, 2008 1:25 am
Location: India
Contact:
Post
by adroit » Wed Oct 08, 2008 5:13 am
Hi Friend,
Use this query
update jos_users set expiryDate= DATE_ADD(expiryDate, INTERVAL 1 YEAR);
Regards,
Pravin
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 08, 2008 5:23 am
adroit wrote: Hi Friend,
Use this query
update jos_users set expiryDate= DATE_ADD(expiryDate, INTERVAL 1 YEAR);
Regards,
Pravin
First, it's not going to work.
Second - do you think that giving
adroit copy-paste examples is good for him?
There are 10 types of people in this world, those who understand binary and those who don't
adroit
Forum Commoner
Posts: 37 Joined: Fri Aug 08, 2008 1:25 am
Location: India
Contact:
Post
by adroit » Wed Oct 08, 2008 5:27 am
Hi Friend,
Can you please let me know why this will not work.
Thanks in advance.
Regards,
Pravin
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Wed Oct 08, 2008 5:30 am
expiryDate has no value (i.e. NULL or 0000-00-00) before executing your query
There are 10 types of people in this world, those who understand binary and those who don't
adroit
Forum Commoner
Posts: 37 Joined: Fri Aug 08, 2008 1:25 am
Location: India
Contact:
Post
by adroit » Wed Oct 08, 2008 5:38 am
Hi,
Oh I didn't saw the registerDate
May this will work
update jos_users set expiryDate= DATE_ADD(registerDate, INTERVAL 1 YEAR);
Regards,
Pravin
arunkar
Forum Commoner
Posts: 50 Joined: Mon Feb 25, 2008 10:37 pm
Post
by arunkar » Wed Oct 08, 2008 5:47 am
Thanks VladSun and adroit.
Now I know I shouldn't use subselection for the same row...
thanks again guys...