CODEWITHSUNDEEP
sql
pikarie
pikarie

Reputation: 193

How to update sql table join on 3 columns

I'm trying to update my table Products but only for the rows that are in my temporary table, see below:

Table Products

PkProduct   Price   DateChanged 
1           4.99    2013-01-01  
1           5.99    2014-01-01  
2           10.50   2000-01-01  
2           12.50   2002-01-01  
2           14.50   2003-01-01  
3           0.99    2000-01-01  
4           0.99    2000-01-01  
5           0.99    2000-01-01  

Temporary Table #temptable

PkProduct   Price   DateChanged
2           12.50   2002-01-01
2           14.50   2003-01-01

You can see that my #temptable contain row equal to the table Products I want to set a new price for both those rows let say @variableNewPrice = 2.75

I'm trying to create a join in my update code:

UPDATE Products
SET Price = @variableNewPrice
FROM Products p1
RIGHT JOIN #temptable p2
ON p2.PkProduct = p1.PkProduct 
AND p2.Price = p1.Price
AND P2.DateChanged = p1.DateChanged

It's not working really well. I tried with only the PkProduct = PkProduct but then every row for this PkProduct where changed! I don't have a column PkProduct that is a real unique Pk for the table.

I appreciate your help =)

Upvotes: 0

Views: 56

Answers (1)

Siyual
Siyual

Reputation: 16917

This should be the correct syntax for what you're trying to do:

UPDATE  P
SET     Price = @variableNewPrice
FROM    Products    P
JOIN    #temptable  T   ON  P.PkProduct = T.PkProduct 
                        AND P.Price = T.Price
                        AND P.DateChanged = T.DateChanged

There were two issues with the query - the last ON clause was T.DateChanged = T.DateChanged which would always be true. The other issue is that you were also doing an OUTER JOIN which doesn't make sense in this context. I modified the query to use an INNER JOIN.

Upvotes: 1

Related Questions