How do I update a table in SQL from another existing table on SQL?-CodePudding

I have the following SQL which it creates a new table called NightTopHit form the existing table called winNumber

CREATE TABLE NightTopHit
AS (SELECT 
winNumber,
COUNT(winNumber) 
FROM
ganadoresdia
group by
winNumber
having COUNT(winNumber) > 1
ORDER BY count(winNumber) DESC);

Since the table winNumber is updated daily I would like to update table NightTopHit without dropping the table and running the code above every time.

I tried the SQL below but it does not work not sure how to use SET. Does anyone knows how I can update the table?

UPDATE TABLE NightTopHit
AS (SELECT 
winNumber as 'Numero Ganador',
COUNT(winNumber) as 'Veces Ganadas'
FROM
ganadoresdia
group by
winNumber
having COUNT(winNumber) > 1
ORDER BY count(winNumber) DESC);

CodePudding user response：

Well since the structure of the table NightTopHit is not changing you can first truncate the table as follows

TRUNCATE TABLE NightTopHit;

and then fill it up with the updated data again using your query as follows

INSERT INTO NightTopHit
(SELECT 
winNumber as 'Numero Ganador',
COUNT(winNumber) as 'Veces Ganadas'
FROM
ganadoresdia
group by
winNumber
having COUNT(winNumber) > 1
ORDER BY count(winNumber) DESC);

This will insure that the table will contain the updated data always and you don't have to drop it and recreate it every time there is an update.