Update Across Joined Tables in MySQL
I had to do a MySQL update on records that were retrieved from a two-table join today. I shouldn’t have been surprised to find that I needed a different syntax than I know from MS SQL, where I would write,
|
1 2 3 4 5 6 7 8 |
UPDATE fTime SETExportStatus = 1 FROM fTime INNER JOIN fProjects ON fTime.ProjectID = fProjects.ProjectID WHERE ContractID = 1 AND WorkDate <= '2013-11-23' AND ExportStatus = 0 |
I always thought this was a goofy syntax, but I’ve gotten used to it. If it isn’t clear, I’m finding all of the records in fTime that belong to a particular record in parent table fProject with the conditions that the WorkDate is before a particular date and the ExportStatus is 0. Those are just columns in fTime. They aren’t particularly important to this discussion.
To do the same thing in MySql, I have to write,
|
1 2 3 4 5 6 7 |
UPDATE fTime INNER JOIN fProjects ON fTime.ProjectID = fProjects.ProjectID SET ExportStatus = 1 WHERE ContractID =1 AND WorkDate <= '2013-11-23' AND ExportStatus = 0 |
Now that I look at it, the MySql syntax is a lot more intuitive to me. Too bad I’ll never remember it, since I use MySql so much less than I use MS SQL.
Of course, with MySql, I have to be really specific with my date format too. But that’s another topic for another day.