Update table in SQLite based on other table-CodePudding

I have two tables, A and B. Due to wrongly specified loop I need to delete some rows from table A (25k rows).

The tables looks as follows:

CREATE TABLE "A" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "front" INTEGER,
    "side"  INTEGER,
    "region"    INTEGER
);

and

CREATE TABLE "B" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "R1"    INTEGER,
    "R2"    INTEGER,
    "R3"    INTEGER,
    "R4"    INTEGER,
);

The columns R1, R2, R3 and R4 specify which course (from table A) was used in that round (from table A). To show whats going wrong in Table A

33  2016    895 1   1   12  5   L
33  2016    895 1   2   18  10  R
33  2016    895 1   3   15  7   R
33  2016    895 1   4   11  7   R
33  2016    895 1   5   18  7   L
33  2016    895 1   6   28  5   L
33  2016    895 1   7   21  12  R
33  2016    895 1   8   14  4   L
33  2016    895 1   9   10  5   R
33  2016    895 1   10  11  4   R
33  2016    880 1   1   12  5   L
33  2016    880 1   2   18  10  R
33  2016    880 1   3   15  7   R
33  2016    880 1   4   11  7   R
33  2016    880 1   5   18  7   L
33  2016    880 1   6   28  5   L
33  2016    880 1   7   21  12  R
33  2016    880 1   8   14  4   L
33  2016    880 1   9   10  5   R
33  2016    880 1   10  11  4   R
33  2016    715 1   1   12  5   L
33  2016    715 1   2   18  10  R
33  2016    715 1   3   15  7   R
33  2016    715 1   4   11  7   R
33  2016    715 1   5   18  7   L
33  2016    715 1   6   28  5   L
33  2016    715 1   7   21  12  R
33  2016    715 1   8   14  4   L
33  2016    715 1   9   10  5   R
33  2016    715 1   10  11  4   R

Table B looks for this particular example like

33  2016    715 715 715 715

So, the data should only have been inserted for course 715.

I think I need to loop over B and get the course-codes for each tournament-year-round combination and only keep these data buckets in A. How can I do this? Thanks

CodePudding user response：

The simplest way to get all the Rx values from table B is with UNION in a CTE.
Then use NOT IN in the DELETE statement to delete all rows of table A with a course that does not exist in the CTE:

WITH cte AS (
  SELECT R1 FROM B 
  UNION 
  SELECT R2 FROM B 
  UNION 
  SELECT R3 FROM B 
  UNION 
  SELECT R4 FROM B
)
DELETE FROM A
WHERE course NOT IN cte;

See the demo.

or, with NOT EXISTS:

DELETE FROM A
WHERE NOT EXISTS (
  SELECT *
  FROM B
  WHERE A.course IN (B.R1, B.R2, B.R3, B.R4)
);

See the demo.

If you need the columns tournament and year in the conditions also, change to:

WITH cte AS (
  SELECT tournament, year, R1 FROM B 
  UNION 
  SELECT tournament, year, R2 FROM B 
  UNION 
  SELECT tournament, year, R3 FROM B 
  UNION 
  SELECT tournament, year, R4 FROM B
)
DELETE FROM A
WHERE (tournament, year, course) NOT IN cte;

See the demo.

or:

DELETE FROM A
WHERE NOT EXISTS (
  SELECT *
  FROM B
  WHERE B.tournament = A.tournament
    AND B.year = A.year  
    AND A.course IN (B.R1, B.R2, B.R3, B.R4)
);

See the demo.