SQL Update using values from other columns in same table

Question

I have an SQL table, "data", with the following columns: id (int), date1 (datetime), date2 (datetime), difference (float)

What I would like to do is update this table so that the "difference" column contains the difference in years (as a float) between date2 and date1, only when date2 doesn't equal '0000-00-00 00:00:00'

I was thinking of something along the lines

Update data m
SET difference = datediff("hh",m.date2, m0.date1)/8765 --there are 8765 hours per year so this should give me my float
FROM data m0
WHERE m.id = m0.id
AND m.date2 <> '0000-00-00 00:00:00';

Yet when I try this I get an error stating "SQL syntax error. Check your MySQL server version for the right syntax to use near data SET difference = datediff("hh,m.date2,m0.date10/8765) from 'd' at line 1"

How would I modify my sql statement to get the desired results?

EDIT:

I am using xampp's phpMyAdmin interface to run this sql statement

`Update data SET difference = datediff('hh',date2, date1)/8765 where date2 <> '0000-00-00 00:00:00'` — Iłya Bursov, Mar 16 '15 at 19:48
update [...] set [...] from is not a valid update statement. — Jonast92, Mar 16 '15 at 19:48
possible duplicate of [mysql update join](http://stackoverflow.com/questions/15209414/mysql-update-join) — Jonast92, Mar 16 '15 at 19:57

score 0 · Answer 1 · edited May 23 '17 at 10:24

Update statements are suppose to be on the form:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE conditions;

Your from clause is thus irrelevant.

You can use joins and sub select-queries though to link to other tables.

-- There are excellent demos on how to achieve that (actually, a complete duplicate):

UPDATE TABLEA a 
JOIN TABLEB b ON a.join_colA = b.join_colB  
SET a.columnToUpdate = [something]

score 0 · Answer 2 · answered Mar 16 '15 at 19:59

0

I figured it out, thanks to Norbert's advice

I used

update data m, data m0
set m.difference = datediff(date(m.date2),date(m0.date2))/365
where m.id = m0.id
and m.date2 <> '0000-00-00 00:00:00';

I was using the incorrect syntax for mysql datediff, which takes 2 dates as a parameter and returns the difference in days.

answered Mar 16 '15 at 19:59

Duncan

9
2

you don't need to join to update the same table, you're wasting resources – Iłya Bursov Mar 16 '15 at 20:50

score -1 · Answer 3 · answered Mar 16 '15 at 19:49

-1

Try this syntax for update with multiple tables:

Update data m, data m0
SET m.difference = datediff("hh",m.date2, m0.date1)/8765 
WHERE m.id = m0.id
AND m.date2 <> '0000-00-00 00:00:00';

Note that in the SET statement you also need to indicate which table you are updating else it will still give you a nice warning.

answered Mar 16 '15 at 19:49

Norbert

6,026
3
17
40

Thanks, still some people has actively down voted the answer though. – Norbert Mar 16 '15 at 20:04
You deserve better, Norbert :) – JustAPup Mar 16 '15 at 20:29

SQL Update using values from other columns in same table

3 Answers3