I have following two tables:
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
I'm trying to assign for each student number of allowed trips based on some conditions. My query is:
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
But this query doesn't work. What am I doing false? How can I get this code worked?