Update rows based on WHERE from INNER JOIN-CodePudding

I need to update a column's value when a row exists based on multiple conditions within a join. I've got the following:

SELECT *
FROM Fixtures f  
INNER JOIN Results r ON f.FixtureID = r.FixtureID 
WHERE f.Status = 1
AND f.IsResult = 0
AND f.Season = 1
AND r.TeamID IS NOT NULL
AND (DATEDIFF(NOW(), f.Date) >= 2)

Which returns a single row that matches, I'd like to then update f.IsResult so have done the following:

UPDATE f
SET f.IsResult = 1
FROM Fixtures f  
INNER JOIN Results r ON f.FixtureID = r.FixtureID 
WHERE f.Status = 1
AND f.IsResult = 0
AND f.Season = 1
AND r.TeamID IS NOT NULL
AND (DATEDIFF(NOW(), f.Date) >= 2)

However I get an error when trying this #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 Fixtures f INNER JOIN Results r ON f.FixtureID = r.FixtureID WHERE f.S' at line 3

CodePudding user response：

Try this:

UPDATE Fixtures 
SET Fixtures.IsResult = 1
WHERE Fixtures.Status = 1
AND Fixtures.IsResult = 0
AND Fixtures.Season = 1
AND (SELECT TeamID FROM Results WHERE Fixtures.FixtureID = Results.FixtureID) IS NOT NULL
AND (DATEDIFF(NOW(), Fixtures.Date) >= 2)

By using (Results) in a subquery, UPDATE statement has a single table (Fixtures) to target.