Updating row of table Using data from multiple columns of another table.

Question

I have following table which I want to update using another table, given below.

I want to update Null values of above given table using following table on the basis of ProductId.

The updated table should be like this.

I have mentioned ProductId in these table just for example. I don't know exact ProductId. It could be any ProductId.

I know FieldId and FieldValue in advance in 2nd table.

Can I do this in one UPDATE statement for all columns.

Answer 1

In SQL Server, an UPDATE statement allows a FROM clause with JOINS. For example, this query would update the Rating field:

UPDATE      p
SET         p.Rating = pf.FieldValue
FROM        Products p
INNER JOIN  ProductField pf
ON          pf.ProductId = p.ProductId
WHERE       pf.FieldId = 50

You could copy this query for the other fields. It's also possible to update more fields in query, but that seems unnecessary in this case.

Answer 2

Maybe something like this:

Update T1
Set T1.RatingID = T2.FieldID,
T1.Rating = T2.FieldValue
From Table1 T1
Inner JOin Table2 T2
On T1.ProductID = T2.ProductID
Where T2.FieldID = 50

To edit all the columns at once, you would need to use subquries:

Update T1
Set T1.RatingID = (Select T2.FieldID
                   From Table2 T2
        Where T2.FieldID = 50
         And T2.ProductID = T1.ProductID)
From Table1 T1

Answer 3

You will first need to transform your second table so that it contains only one row per ProductID.

SELECT t1.ProductID, t1.FieldID AS RatingID, t1.FieldValue AS Rating, 
       t2.FieldID AS LeisureID, t2.FieldValue AS Leisure, etc.
FROM SecondTable t1
LEFT OUTER JOIN SecondTable t2 
       ON t1.ProductID = t2.ProductID 
       AND t2.FieldValue = 55
LEFT OUTER JOIN SecondTable t3 
       ON t1.ProductID = t3.ProductID 
       AND t3.FieldValue = 60
WHERE t1.FieldValue = 50

Then you can update all columns in the first table from this table in one update query. Note that you could make the above a View of the Second table so this would be easier to use later. (We'll call it SecondTableView for now; incidentally it has the exact form of the first table now).

UPDATE FirstTable 
SET RatingID = t1.RatingID, Rating = t1.Rating, etc.
FROM SecondTableView t1
WHERE FirstTable.ProductID = t1.ProductID

The issue with this approach is that you must know all of the possible Fields for each Product ahead of time but that is pretty much required anyway because of the table schema being fixed.

Answer 4

In SQL Server, the PIVOT keyword turns rows into columns. We need two PIVOTs, one for FieldId and one for FieldValue. The ;WITH keyword (which is preceded by a semicolon to distinguish it from the unrelated WITH ROLLUP command) allows us to use create "temporary views" which we use later in the UPDATE statement.

;WITH FieldIds AS (SELECT * FROM (SELECT ProductId, FieldId FROM ProductFields) A
                   PIVOT (MAX(FieldId) FOR FieldId IN ([50], [55], [60])) AS B),
      FieldValues AS (SELECT * FROM ProductFields
                      PIVOT (MAX(FieldValue) FOR FieldId IN ([50], [55], [60])) AS C)
UPDATE Products
SET
    RatingId = FieldIds.[50], 
    Rating = FieldValues.[50],
    LeisureId = FieldIds.[55],
    Leisure = FieldValues.[55], 
    SpaId = FieldIds.[60],
    Spa = FieldValues.[60]
FROM Products
INNER JOIN FieldIds ON FieldIds.ProductId = Products.ProductId
INNER JOIN FieldValues ON FieldValues.ProductId = Products.ProductId