sql - BigQuery：使用子查询和内部联接的计数更新行

Question

我有以下两个表：

Table 1: Students
name:string,
age:integer,
class:integer,
number_of_allowed_trips:integer

Table 2: Trips
allowed_age:integer,
trip_name:string,
class:integer

我正在尝试根据某些条件为每个学生分配允许的旅行次数。我的查询是：

UPDATE
  `mydataset.students` AS s
SET
  s.number_of_allowed_trips=(
  SELECT
    COUNT(*)
  FROM
    `mydataset.trips` AS t
  WHERE
    t.r.class= 9)
FROM
  `mydataset.trips` AS t
WHERE 
  r.name = 'Jack' 
  AND r.class = 9
  AND w.class = r.class

但是这个查询不起作用。我在做什么假？我怎样才能让这个代码工作？

标签： sqlgoogle-bigquery

Answer 1

UPDATE
  `mydataset.students` AS s
SET
  s.number_of_allowed_trips= (
  SELECT
    COUNT(*)
  FROM
    `mydataset.trips` AS t
  WHERE
    t.class = s.class
    AND s.age= 9  )
WHERE
  s.id=s.id

这很好用！