update on inner join of 2 tables with where clause
I am trying to do an update on an inner join of 2 tables with a where clause. I currently have a select that get's the results I want to update. But I keep getting an error while trying to do the update. Here is my SELECT that works :
SELECT a.rd_1, total_points FROM bracket.ticket as a Inner join bracket.picks as b where b.team_ID = 31 AND a.ID = b.ticket_ID
And here is my update that doesn't work
UPDATE a set a.rd_1 = a.rd_1 + 1 FROM bracket.ticket as a Inner join bracket.picks as b where b.team_ID = 31 AND a.ID = b.ticket_ID
I have also tried this:
UPDATE a SET a.rd_1 = a.rd_1 + 1 FROM bracket.ticket as a Inner join bracket.picks as b on a.ID = b.ticket_ID where b.team_ID = 31 AND a.ID = b.ticket_ID
There error I get here is: Syntax error: 'FROM' (from) is not a valid input at this position.
ID | rd_1 | total_points
ID | ticket_ID | team_ID
Here is the correct syntax in MySQL:
UPDATE bracket.ticket t INNER JOIN bracket.picks p ON t.ID = p.ticket_ID SET t.rd_1 = t.rd_1 + 1 WHERE p.team_ID = 31;
Your syntax looked more like SQL Server.
Note: I changed the table aliases to be abbreviations for the table. Meaningful table aliases (such as abbreviations) make the query more understandable.