title: Syntax Error : SQL update with count and inner join
body: <p>I have two tables:</p>
  
  <pre><code>CREATE TABLE user (
  ticketId integer not null,
  eventId integer not null,
  ticketUsed integer, 
  userName text not null,
  FOREIGN KEY (ticketId) REFERENCES ticketType (ticketId)
  );
  </code></pre>
  
  <p>and </p>
  
  <pre><code>CREATE TABLE ticketType ( 
  eventId integer not null, 
  ticketId integer unique not null,
  ticketsUsed integer,
  FOREIGN KEY (eventId) REFERENCES event (eventId)
  );
  </code></pre>
  
  <p>I'm trying to update <code>ticketType.ticketsUsed</code> according to used tickets</p>
  
  <pre><code>UPDATE ticketType 
  SET ticketsUsed = (SELECT COUNT(*) FROM user as u WHERE ticketUsed=1)
  WHERE ticketId = u.ticketId;
  </code></pre>
  
  <p>But I get an error:</p>
  
  <blockquote>
    <p>Error: no such column: u.ticketId</p>
  </blockquote>
  
  <pre><code>UPDATE ticketType
  SET ticketsUsed = us.count
  FROM ticketType
  INNER JOIN (SELECT ticketId, COUNT(*) as count FROM user  WHERE ticketUsed=1 group by ticketId) us 
  ON us.ticketId=ticketType.ticketId
  WHERE eventId = 23779;
  </code></pre>
  
  <blockquote>
    <p>Error: near "FROM": syntax error</p>
  </blockquote>