SQL Query/stored Procedure Update One to Many

Question

I hope someone can help as I have been struggling with this for some time.

Expanding on a question I have asked previously

https://learn.microsoft.com/en-us/answers/questions/1257532/sql-query-stored-procedure-one-to-many

my code for retrieving product colors is below

I am now trying to Update a Products color/colors.

I am currently looking at the following three situations

1. ProductId = 1 Color3 Update to Yellow
2. ProductId =2 Color2 Insert new color Green
3. ProductId =4 Currently has no colors and therefore insert new color Red

Thank you for your assistance.

Answer 1

For the first example_

UPDATE tbl
SET colour = 'Yellow'
WHERE ProductID = 1
   AND Colour = 'Red

You need to go back to the base table. Referring to red as colur 3 makes no sense. Let's say that Green instead was Violet. The Red is number 2, but it is still Red you want to replace with Yellow.

For the others:

INSERT tbl(ProductId, Colour)
   SELECT V.ProductID, Colour 
   FROM   (VALUES(2, 'Green')) AS V(ProductUD, Colour)
   WHERE NOT EXISTS (SELECT *
                    FROM    tbl t
                    WHERE   t.ProductId = V.ProductID
                      AND   t.Colour = V.Colour)

Answer 2

Hi @BigH61

I can not understand your thoughts. Do you want to update before querying, or query first and then update the result?

The method provided by Erland is an update to the base table.

What I will provide is to move the query result to a temp table and then update the temp table (Although I do not think it makes sense).

;with cteColor as (
Select ProductId, Color,row_number() over(partition by productid order by color) rn
from ProductColorTable)
select ProductId,
Max(Case when rn=1 then Color else null end) Color1,
Max(Case when rn=2 then Color else null end) Color2,
Max(Case when rn=3 then Color else null end) Color3,
Max(Case when rn=4 then Color else null end) Color4,
Max(Case when rn=5 then Color else null end) Color5
into #temp from cteColor
Group by ProductId;

update #temp set Color3 = 'Yellow' where ProductId = 1;
update #temp set Color2 = 'Green' where ProductId = 2;
insert into #temp(ProductId,Color1) values(4,'Red');
select * from #temp;

Output:

Best regards,

Percy Tang