I have a Circus table as follow

and a Ticket table as follow

and i want to alter the circus table by adding a new column called ticket_sold and the value should be as follow

this is what I have tried

 alter table circus add ticket_sold numeric(3) default 0;
 update circus set ticket_sold = (select count(ticket_id) from ticket group by circus_id);

it gives me an error said

 single-row subquery returns more than one row

CodePudding user response：

In-general, don't, as you will end up with a ticket_sold column that rapidly becomes out-of-sync with the ticket table.

If you want to have a dynamically updating column then:

1. Use a view.

You can just compute the value whenever you need it:

CREATE VIEW circus_view (circus_id, circus_date, circus_show_price, tickets_sold) AS
SELECT c.circus_id,
       c.circus_date,
       c.circus_show_price,
       (SELECT COUNT(*) FROM ticket t WHERE t.circus_id = c.circus_id)
FROM   circus c;

2. Use a trigger.

If you must persist the number of tickets in the circus table then:

ALTER TABLE Circus ADD tickets_sold NUMBER;

CREATE TRIGGER circus_tickets
  AFTER INSERT OR UPDATE OR DELETE ON Ticket
BEGIN
  UPDATE Circus c
  SET tickets_sold = (SELECT COUNT(*) FROM ticket t WHERE t.circus_id = c.circus_id);
END;
/

fiddle

CodePudding user response：

Is is not group by clause you need because query then returns number of tickets per each circus, but - then you get as many rows as there are circus_ids in the ticket table. Instead, correlate subquery to the main table:

update circus c set 
  c.ticket_sold = (select count(t.ticket_id) 
                   from ticket t
                   where t.circus_id = c.circus_id
                  );