Spring Data JPA Query: Prepopulate/update one table based on total amount of true/false values from another table

I have an issue with populating a table based on the results from another table. Table 1 collects submission data for a user on a given project. The submission data collected consists of tasks that the user carried out on the project and is stored in the table as a boolean value (true if they carried out the task and false if they haven't). The table values can be found in the Entity class below:

@Entity
@Table(name = "trackersubmission")
public class TrackerSubmit {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "ts_id")
	private int tsid;
	
	@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
	private User user;
	
	@Column(name = "user_id")
	private int user_id;
	
	@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "manager_id", insertable = false, updatable = false)
	private User manager;
	
	@Column(name = "manager_id")
	private int manager_id;
	
	@ManyToOne(targetEntity = Project.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "project_id", insertable = false, updatable = false)
	private Project project;
	
	@Column(name = "project_id")
	private int project_id;
	
	@Column(name = "projectname")
	private String projectname;
	
	@Column(name = "enddate")
	private Date enddate;
	
	@Column(name = "completiondate")
	private Date completiondate;
	
	@Column(name = "osra")
	private boolean osra;
	
	@Column(name = "wsra")
	private boolean wsra;
	
	@Column(name = "quoting")
	private boolean quoting;
	
	@Column(name = "scoping")
	private boolean scoping;
	
	@Column(name = "idcopier")
	private boolean idcopier;
	
	@Column(name = "componentchecker")
	private boolean componentchecker;
	
	@Column(name = "nodetool")
	private boolean nodetool;
	
	@Column(name = "review")
	private boolean review;
	
	@Column(name = "leadrole")
	private boolean leadrole;
	
	@Column(name = "parc")
	private boolean parc;
	
	@Column(name = "onsite")
	private boolean onsite;
	
	@Column(name = "comment")
	private String comment;
	
	@Column(name = "statuscheck")
	private boolean statuscheck;
	
	@Column(name = "managercomment")
	private String managercomment;

	public TrackerSubmit(int tsid, User user, int user_id, User manager, int manager_id, Project project,
			int project_id, String projectname, Date enddate, Date completiondate, boolean osra,
			boolean wsra, boolean quoting, boolean scoping, boolean idcopier, boolean componentchecker,
			boolean nodetool, boolean review, boolean leadrole, boolean parc, boolean onsite, String comment,
			boolean statuscheck, String managercomment) {

		this.tsid = tsid;
		this.user = user;
		this.user_id = user_id;
		this.manager = manager;
		this.manager_id = manager_id;
		this.project = project;
		this.project_id = project_id;
		this.projectname = projectname;
		this.enddate = enddate;
		this.completiondate = completiondate;
		this.osra = osra;
		this.wsra = wsra;
		this.quoting = quoting;
		this.scoping = scoping;
		this.idcopier = idcopier;
		this.componentchecker = componentchecker;
		this.nodetool = nodetool;
		this.review = review;
		this.leadrole = leadrole;
		this.parc = parc;
		this.onsite = onsite;
		this.comment = comment;
		this.statuscheck = statuscheck;
		this.managercomment = managercomment;
	}

//getters and setters

Additionally, Table 2 is an overview of the first which should collect the total number of each task carried out by each user over all the projects they have carried out. This can be seen below:

@Entity
@Table(name = "prooverview")
public class ProjectPerformance {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "po_id")
	private int poid;
	
	@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
	private User user;
	
	@Column(name = "user_id")
	private int user_id;
	
	@Column(name = "audits")
	private int audits;
	
	@Column(name = "osra")
	private int osra;
	
	@Column(name = "wsra")
	private int wsra;
	
	@Column(name = "quoting")
	private int quoting;
	
	@Column(name = "scoping")
	private int scoping;
	
	@Column(name = "idcopier")
	private int idcopier;
	
	@Column(name = "componentchecker")
	private int componentchecker;
	
	@Column(name = "nodetool")
	private int nodetool;
	
	@Column(name = "review")
	private int review;
	
	@Column(name = "leadrole")
	private int leadrole;
	
	@Column(name = "parc")
	private int parc;
	
	@Column(name = "onsite")
	private int onsite;

	public ProjectPerformance(int poid, User user, int user_id, int audits, int osra, int wsra, int quoting, int scoping,
			int idcopier, int componentchecker, int nodetool, int review, int leadrole, int parc, int onsite) {
		this.poid = poid;
		this.user = user;
		this.user_id = user_id;
		this.audits = audits;
		this.osra = osra;
		this.wsra = wsra;
		this.quoting = quoting;
		this.scoping = scoping;
		this.idcopier = idcopier;
		this.componentchecker = componentchecker;
		this.nodetool = nodetool;
		this.review = review;
		this.leadrole = leadrole;
		this.parc = parc;
		this.onsite = onsite;
	}
//getters and setters

I have created the following Query statement in an attempt to update Table 2 based on submissions in Table 1.

	@Query("UPDATE ProjectPerformance p INNER JOIN "
		+ "(SELECT user_id, osra, wsra, quoting, scoping, idcopier, componentchecker, nodetool, review, leadrole, parc, onsite, statuscheck FROM TrackerSubmit GROUP BY user_id WHERE statuscheck = TRUE ) x"
		+ "ON p.user_id = x.user_id"
		+ "SET p.osra = COUNT(x.osra = TRUE), p.wsra = COUNT(x.wsra = TRUE). p.quoting = COUNT(x.quoting = TRUE), p.scoping = COUNT(x.scoping = TRUE), p.idcopier = COUNT(x.idcopier = TRUE, ), p.componentchecker = COUNT(x.componentchecker = TRUE), p.nodetool = COUNT(x.nodetool = TRUE), p.review = COUNT(x.review = TRUE), p.leadrole = COUNT(x.leadrole = TRUE), p.parc = COUNT(x.parc = TRUE), p.onsite = COUNT(x.onsite = TRUE)")

However, when I attempt to run this query I am flagged with the SQL error:

2020-07-31 10:22:15.456 ERROR 2452 --- [           main] o.h.hql.internal.ast.ErrorTracker        : line 1:22: expecting "set", found 'INNER'
2020-07-31 10:22:15.458 ERROR 2452 --- [           main] o.h.hql.internal.ast.ErrorTracker        : line 1:22: expecting "set", found 'INNER'

antlr.MismatchedTokenException: expecting "set", found 'INNER'
	at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]

Any help is much appreciated!

Thanks!!

Appears to be an issue with your query.

Few imp points

1. Update needs to be followed by SET
2. Update can be done on one table at a time. So if you want to make an update, the query will be line

update table set field = (select column from table a join table b on condition where condition`) or something similar.