0

I did an INSERT INTO of data from an import table into a new table I created which has general info about people, name, address, dob, age, sex, etc.

When I did the insert into my new tables, the import data didn't have age for the people, so I did the following after the INSERT INTO :

UPDATE table_a set age = (SELECT timestampdiff(year, dob, curdate()) AS age);

which instantly gave me everyone's age and updated the column accordingly.

Now I want to know who's having a birthday based on current date:

SELECT name, dob FROM table_a WHERE DAYOFYEAR(dob) - DAYOFYEAR(CURDATE()) = 0;

My question is: How do I do an update based when their dob = current date?

This is what I tried:

UPDATE table_a set age = (SELECT timestampdiff(year, dob, curdate()) AS age WHERE DAYOFYEAR(dob) - DAYOFYEAR(CURDATE()) = 0);

This is the error I get:

Error Code: 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 'WHERE DAYOFYEAR(dob) - DAYOFYEAR(CURDATE()) = 0)' at line 1

I don't understand what I am doing wrong...

Running MySQL 5.6.25

Thank you in advance for any replies.

kittykittybangbang
  • 2,380
  • 4
  • 16
  • 27
devNub
  • 25
  • 6
  • Try removing `AS Age` – artm Jul 31 '15 at 14:19
  • Since the age changes constantly you probably don't want to store that in the database at all but rather calculate it whenever you need to know the age. Otherwise you would have to update it every day to keep it accurate. – jpw Jul 31 '15 at 14:21
  • I'm meshing this with php, and I'm having it where if they, search for a person, it'll do a check to see what their dob is and IF its today, update the age at that point. But, yes I see your point. – devNub Jul 31 '15 at 14:27
  • Why would you store age? That's beyond... well, I better not say. – Strawberry Jul 31 '15 at 14:30
  • its part of a bigger algorithm, thats gonna take age into account. – devNub Jul 31 '15 at 14:41
  • There's no reason to store age as it's constantly changing. Wouldn't you want someone's current age at all times, instead of whatever their age happened to have been whenever you happened to have updated that column? If you need to use that value in another algorithm as you say, then use a calculated column in the subquery to get the current age at all times instead of messing about with updates which may or may not fire. – Michael McGriff Jul 31 '15 at 14:53

2 Answers2

1

EDIT:

This is the query you need to update age for users who has dob on current day.

UPDATE table_a a
SET a.age = TIMESTAMPDIFF(YEAR, dob, CURDATE())
WHERE a.id IN (
      SELECT id
      FROM (SELECT * FROM table_a)  AS temp_table
      WHERE DATE_FORMAT(dob,'%m-%d') = DATE_FORMAT(CURDATE(),'%m-%d')
)

SQLFIDDLE.

You can't use DAYOFYEAR as it considers leap years i.e. this method will return 212 for 2015-07-31 but will return 213 for 2012-07-31.

Secondly, to use the same table in update query, it should be used as temporary table. Explanation here.

Reason for SQL Syntax error is that you're missing FROM clause in inner query. i.e. SELECT timestampdiff(year, dob, curdate()) AS age WHERE .... Add FROM table_a.

Community
  • 1
  • 1
AimZ
  • 526
  • 2
  • 9
  • I tried your suggestion its telling me this: UPDATE table_a set age = (SELECT timestampdiff(year, dob, curdate()) as age from table_a WHERE DAYOFYEAR(dob) - DAYOFYEAR(CURDATE()) = 0); Error Code: 1093. You can't specify target table 'table_a' for update in FROM clause – devNub Jul 31 '15 at 14:33
  • http://stackoverflow.com/questions/9285143/mysql-you-cant-specify-target-table-a-for-update-in-from-clause – splash58 Jul 31 '15 at 14:45
  • Updated the answer with working query and sqlfiddle. – AimZ Jul 31 '15 at 15:46
0

You can use same WHERE clause for your update like in example:

  SELECT name, dob FROM table_a
   WHERE MONTH(dob) = MONTH(CURRENT_TIMESTAMP) AND
         DAYOFMONTH(dob) = DAYOFMONTH(CURRENT_TIMESTAMP)

Sqlfiddle

Full query text to update:

 UPDATE 
   table_a set 
      age = timestampdiff(year, dob, curdate()) 
   WHERE MONTH(dob) = MONTH(CURRENT_TIMESTAMP) AND
         DAYOFMONTH(dob) = DAYOFMONTH(CURRENT_TIMESTAMP);

And it works fine with MySQL 5.6 in Sqlfiddle

Stepan Novikov
  • 1,402
  • 12
  • 22