Eric Pratt Eric Pratt - 29 days ago 11
SQL Question

Oracle PL/SQL Merge Query Result into Table Column

I have a database that stores golf related data. I'm trying to count the number of eagles, birdies, pars etc for each hole on each course and insert the quantity into the corresponding attribute in the hole table.

I can write the queries to pull the information I'd like, but I'm not sure how to take my query result and merge it into the corresponding records within my table Hole. I've looked at the documentation for the Oracle SQL's MERGE, but haven't had any success.

Here's what I have right now:

--Count all the birdies on holes 1-18 of course 538
select phs.course_id, phs.hole_num, count(*) from player_hole_score phs
join hole h on
phs.hole_num = h.hole_num and
phs.course_id = h.course_id
where phs.score = h.hole_par - 1 and phs.course_id = 538
group by phs.hole_num, phs.course_id
order by phs.course_id, phs.hole_num;

--Where the data needs to be inserted
select course_id, hole_num, hole_num_birdie from hole
where course_id = 538;


Both query results below:

Query 1 Query 2 (Table Hole)
+-----------+----------+----------+ +-----------+----------+-----------------+
| COURSE_ID | HOLE_NUM | COUNT(*) | | COURSE_ID | HOLE_NUM | HOLE_NUM_BIRDIE |
+-----------+----------+----------+ +-----------+----------+-----------------+
| 538 | 1 | 103 | | 538 | 1 | |
| 538 | 2 | 76 | | 538 | 2 | |
| 538 | 3 | 42 | | 538 | 3 | |
| 538 | 4 | 71 | | 538 | 4 | |
| 538 | 5 | 82 | | 538 | 5 | |
| 538 | 6 | 77 | | 538 | 6 | |
| 538 | 7 | 90 | | 538 | 7 | |
| 538 | 8 | 34 | | 538 | 8 | |
| 538 | 9 | 188 | | 538 | 9 | |
| 538 | 10 | 87 | | 538 | 10 | |
| 538 | 11 | 53 | | 538 | 11 | |
| 538 | 12 | 95 | | 538 | 12 | |
| 538 | 13 | 137 | | 538 | 13 | |
| 538 | 14 | 69 | | 538 | 14 | |
| 538 | 15 | 170 | | 538 | 15 | |
| 538 | 16 | 197 | | 538 | 16 | |
| 538 | 17 | 56 | | 538 | 17 | |
| 538 | 18 | 82 | | 538 | 18 | |
+-----------+----------+----------+ +-----------+----------+-----------------+


How can I take the column COUNT(*) from the first query result and use the counts to update the corresponding records in table Hole so that I get a result like this:

+-----------+----------+-----------------+
| COURSE_ID | HOLE_NUM | HOLE_NUM_BIRDIE |
+-----------+----------+-----------------+
| 538 | 1 | 103 |
| 538 | 2 | 76 |
| 538 | 3 | 42 |
| 538 | 4 | 71 |
| 538 | 5 | 82 |
| 538 | 6 | 77 |
| 538 | 7 | 90 |
| 538 | 8 | 34 |
| 538 | 9 | 188 |
| 538 | 10 | 87 |
| 538 | 11 | 53 |
| 538 | 12 | 95 |
| 538 | 13 | 137 |
| 538 | 14 | 69 |
| 538 | 15 | 170 |
| 538 | 16 | 197 |
| 538 | 17 | 56 |
| 538 | 18 | 82 |
+-----------+----------+-----------------+

Answer
merge into hole
  using (   your query here   ) q
  on (hole.course_id = q.course_id and hole.hole_num = q.hole_num)
when matched
  then update set hole.hole_num_birdie = q.ct
where hole.course_id = 538  --  this is optional, you can update all at once

your query here is your first query, MINUS the ORDER BY clause which is not needed. Notice it is given the alias q in the MERGE statement.

In the first query, you need to give an alias to the count(*) column: count(*) as ct.

Before you do this, though, please do consider what I said in my Comment under your original post.