MySQL concat year month date colums to date column in same table

Question

I have a table 'setlists' in mysql with columns 'year' 'month' 'day' of int type that I would like to str_to_date and concat into a new column 'date' in the same table. This is what I have so far but I cant seem to get the syntax right. It wont run. I have already created the new date column and set it as date type.

UPDATE setlists
SET date = str_to_date(concat(year,'-',month,'-',day), '%Y-%m-%d') FROM setlists

Data:

I am trying to concat the year month day columns into the date column of the same table.

date  year  month  day
null  2018  01     01
null  2017  01     01

The current error output is as follows:

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 'FROM setlists' at line 2

Year month and day are mysql date functions. Try enclosing them in backticks `. — P.Salmon, Apr 01 '18 at 10:48
`It won't run`. What is the output of your query? Do you see errors? If so, post them in your question. — Robert, Apr 01 '18 at 10:50
Please show us some data. @P.Salmon year, month, and day are not reserved keywords. — Tim Biegeleisen, Apr 01 '18 at 10:58
those are the names of the columns in the setlists table, am I not able to concat columns from a table? — Brent Vaalburg, Apr 01 '18 at 11:00

score 4 · Answer 1 · answered Apr 01 '18 at 11:00

4

Just remove FROM setlists at the end of your update query. It does not belong there.

UPDATE setlists
SET date = str_to_date(concat(year,'-',month,'-',day), '%Y-%m-%d');

SQL update syntax is such that the target table is specified as whatever follows UPDATE.

answered Apr 01 '18 at 11:00

Tim Biegeleisen

387,723
20
200
263

Thank you so much, this worked perfect. Of course it was something obvious...haha – Brent Vaalburg Apr 01 '18 at 11:08

Robert · Answer 2 · 2018-04-01T11:43:45.423

1

With update queries you should not use the FROM [table], unless you use a SELECT query for input, you already define which table you want to update with UPDATE setlists.

Remove the FROM setlists from your query to make it work.

See the UPDATE query syntax documentation here

So:

UPDATE setlists
SET date = STR_TO_DATE(CONCAT(year,'-',month,'-',day), '%Y-%m-%d');

See: SQL Fiddle

edited Apr 01 '18 at 11:43

answered Apr 01 '18 at 11:02

Robert

4,840
1
24
29

Updated my answer, I agree that you should write standard SQL. – Robert Apr 01 '18 at 11:44

score 0 · Answer 3 · answered Apr 01 '18 at 11:01

Check the usage of year,month,day functions

   UPDATE setlists
    SET date = str_to_date(concat(year(sysdate()),'-',month(sysdate()),'-',day(sysdate())), '%Y-%m-%d');

If these are columns then

UPDATE setlists
SET date = str_to_date(concat(year,'-',month,'-',day), '%Y-%m-%d');

score 0 · Answer 4 · answered May 19 '21 at 08:10

0

There's a neater way:

DATE(CONCAT_WS('-', year, month, day))

answered May 19 '21 at 08:10

Zon

12,838
4
69
82

MySQL concat year month date colums to date column in same table

4 Answers4