CODEWITHSUNDEEP
sqloracle-database
lala
lala

Reputation: 11

I have a select query that returns a count value

Please help me with the update command 

select guest_no,count(flag_booking) 
from booking 
where flag_booking = 'C' 
group by guest_no;

Now I want to update this using the update command i.e. update the field that will contain the count of flag_booking for every different guest separately 

update guest 
set complete_booking = (
    select count(*) 
    from booking 
    join guest on guest.guest_no = booking.guest_no 
    group by guest.guest_no,guest.flag_booking 
    having guest.flag_booking = 'C'
) ;

This is not working.

Upvotes: 1

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use a correlated subquery:

update guest g
  set complete_booking = (select count(*) 
                          from booking b
                          where b.guest_no = g.guest_no and
                                b.flag_booking = 'C'
                         ) ;

The join and group by do not do what you want. First, the group by is probably going to return multiple rows, which will result in a run-time failure of the query. Second, the subquery is not related to the outer query, so all rows would get the same value.

Upvotes: 1

user330315
user330315

Reputation:

You don't need a join in the subquery, nor a group by:

update guest 
  set complete_booking = (select count(*) 
                          from booking 
                          where guest.guest_no = booking.guest_no) 
                            and booking.flag_booking = 'C');

Upvotes: 0

GMB
GMB

Reputation: 222702

You need to correlate the subquery:

update guestg g
set complete_booking = (
    select count(*) 
    from booking b
    where g.guest_no = b.guest_no and b.flag_booking = 'C'
) ;

In this query, the subquery returns just one record, with a unique column that contains the count of bookings for the concerned guest. Hence you don't need to group by, nor to join.

Upvotes: 1

Related Questions