Search code examples
mysqljoinsql-updatedenormalized

Putting a MySQL JOIN back into the database instead of just returning the columns

I've just got a quick question about a query I'm having difficulty with. I've got two tables and I'm trying to denormalize them into one table.

Here are the two tables:

dateTable:

ID | Date

and dataTable:

ID | DateID | Data

And what I'd like to end up with is:

ID | DateID | Data | Date

What I've got right now is:

UPDATE dataTable
SET date = dateTable.date 
WHERE `DateID` = `dateTable.id`

But this is syntactically incorrect. If I did this with a JOIN, how do you put the data back into the Date column on dataTable and not just return the data?

Thanks!

Solution 
  • UPDATE dataTable data
JOIN dateTable dates on dates.id = data.dataId
SET data.date = dates.date;