Updating a table based on some condition from another table

I have two tables. Table #1 'color' has columns [red], [blue], [green]; where each column is an int type representing the number of times the color was selected, they are counters so to speak.

Table #2 'selected_colors' has columns [person] and [color], where person is not unique. For each selected color by a person there will be one row containing [person] and [color].

My problem is I have since reset the counters in Table #1. I still have the info in Table #2, however. I need to set the color counters based on the query on the Table #2.

Short question: How t开发者_高级运维o use UPDATE from SELECT result?

Without seeing your table definitions or example data and considering you only have 3 colours and this is a once-off exercise try this:

UPDATE [color] SET [red] = SELECT COUNT(1) FROM [selected_colors] WHERE [color] = 'red'

and repeat for blue and green.

Is there any reason you are not using a view for Table#1 instead?

ie

Create View color AS
select r.red,blue,green from

(select count(color) as red from selected_colours where color='red') AS r,
(select count(color) as red from selected_colours where color='blue') AS b,
(select count(color) as red from selected_colours where color='green') AS g

Alternatively your update query should be

update color set red=r.red,blue=b.blue,green=g.green from 
(select count(color) as red from selected_colours where color='red') AS r,
(select count(color) as red from selected_colours where color='blue') AS b,
(select count(color) as red from selected_colours where color='green') AS g

EDIT -- After comment about dynamic colours.

You should create a view

Create View color AS
select color, count(color) as count from selected_colors group by color

This will give you a with a row for each color with its count, You cannot easily create a query that would dynamically change the structure of the results (e.g. changing columns). Its possible but it would be a lot of work with dynamic query creation and it would not be efficient at all.