help for a query

Question

Hi, i've a problem for a table update. follow table structure:

Table1
  tableid
  ...
  ... 
  productID_1
  productID_2
  productID_3

Table2
  productID
  Total

I've to totalize each product in table2.

For example:

SELECT COUNT(*) as tot, ProductID_1 FROM Table1 GROUP Table1 

then the UPDATE table2 SET total =..??? (how can i do) WHERE productID_1 = ....

Hope you can help me.

Thank you

Answer 1

Your options in terms of simplifying the query greatly depend on the product and version you are using. However, a solution that should work in most databases would be:

Update Table2
Set Total = (
            Select Count(*)
            From (
                    Select productId_1 As ProductId From Table1
                    Union All Select productId_2 From Table1
                    Union All Select productId_3 From Table1
                    ) As Z
            Where Table2.ProductId = Z.ProductId
            Group By ProductId
            )

A big reason this query is cumbersome is that the data in Table1 is not normalized. Instead you should consider a structure for Table1 like:

Create Table Table1 (
                    TableId <datatype> not null
                    , ProductId <datatype> not null
                    , Constraint PK_Table1 Primary Key ( TableId, ProductId )
                    )

Answer 2

You can store the first results in a temp table/table variable (if the DB you are using supports it). For instance, in SQL Server, you can do:

declare @t table
(
   key int,
   cnt int
)

insert into @t (key, cnt)
select count(*) as tot, ProductID_1 from Table1 ...

If ProductID_2 and ProductID_3 are in the same table, you can union the results.

Then, insert into table 2:

insert into table2 (productID, Count)
select key, cnt from @t

Answer 3

REPLACE INTO table2
SELECT COUNT(*) as total, ProductID 
FROM Table1 
GROUP Table1.ProductID