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.

ticket

ID | rd_1 | total_points

picks

ID | ticket_ID | team_ID

Answers


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.


Need Your Help

Android list view array adapter

android list view

I am implementing array list adopter uisng "simple_list_item_2" that is inbuilt but I get error. it says " getview from fragment cannot be applied" and it also cannot resolve "get" symbol from get.(

Linqdatasource group by problem with multiple table

asp.net linq-to-sql linqdatasource

I'm using linqdatasource for displaying data in listview (nested because of grouping) control. I want to display data from more than one table.