Skip to content
Advertisement

how to Update an empty table with the result of a query

i have a table name average rating. i want to update my table name final rating with the result of this query

select spid,AVG(avgRat) from AvgRating group by spid

how can i do this?

Take a look at image attached

Advertisement

Answer

Maybe something like

INSERT INTO FinalRating
select spid,AVG(avgRat) from AvgRating group by spid

or if the FinalRating table contains more information you could also specify the columns

INSERT INTO FinalRating (spid, rating)
select spid,AVG(avgRat) from AvgRating group by spid

you can also add a where clause to either query if you need a subset of data.

Edit: If this is not something that is one off and has to be run repeatedly the above solution would duplicate the data

in that case I recommend you merge the tables

Merge FinalRating AS TARGET using 
(select spid,AVG(avgRat) as averageRating from AvgRating group by spid) as SOURCE
on (Target.spid = source.spid)
WHEN MATCHED TEHN
UPDATE SET Target.rating = Source.averageRating
WHEN NOT MATCHED BY TARGET THEN
Insert (spid, averageRating)
VALUES(Source.spid, Source.averageRating)

Notice that I gave the AVG(avgRat) an alias so that I can reference it later

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement