Update table from columns of other table

Question

We have a tables with following data

we want to update the productcost in the table1 from tb_price based on product and city

Can this be done without writing loop ?? Can any one help us on this

Answer 1

You need to unpivot the table tb_price (which may not have the best design).

Also, for this of question it is a good idea to provide data as CREATE TABLE + INSERT has I have done below. This time I typed it myself, so I could test the query, but that's rather an exception.

CREATE TABLE t1 (  
   name    nvarchar(20)   NOT NULL,  
   product nvarchar(20)   NOT NULL,  
   city    nvarchar(20)   NOT NULL,  
   ProductCost int        NULL,  
   CONSTRAINT pk_ti PRIMARY KEY (name, product, city)  
)  
INSERT t1 (name, product, city)  
   VALUES ('n1', 'p1', 'Mumbai'),  
          ('n1', 'p2', 'Pune'),  
          ('n2', 'p2', 'Delhi'),  
          ('n3', 'p1', 'Pune'),  
          ('n3', 'p1', 'Mumbai'),  
          ('n3', 'p1', 'Delhi')  
go  
CREATE TABLE tb_price (product nvarchar(20) NOT NULL,  
                       Mumbai  int          NULL,  
                       Pune    int          NULL,  
                       Delhi   int          NULL,  
                       CONSTRAINT pk_tb_price PRIMARY KEY (product)  
)  
INSERT tb_price(product, Mumbai, Pune, Delhi)  
   VALUES ('p1', 100, 200, 150),  
          ('p2', 90,  98,   95)  
go  
SELECT * FROM t1  
SELECT * FROM tb_price  
go  
; WITH unpvot AS (  
   SELECT p.product, c.city, c.price  
   FROM   tb_price p  
   CROSS  APPLY (VALUES('Mumbai', Mumbai),  
                       ('Pune',   Pune),  
                       ('Delhi',  Delhi)) AS c(city, price)  
)  
UPDATE t1  
SET    ProductCost = u.price  
FROM   t1  
JOIN   unpvot u ON u.product = t1.product  
               AND u.city    = t1.city  
go  
SELECT * FROM t1  
go  
DROP TABLE t1, tb_price  

Answer 2

  ; WITH source AS (  
    SELECT product,'Mumbai' as City, Mumbai as Price from tb_price  
	union all  
	SELECT product,'Pune' as City, Pune  as Price from tb_price  
	union all  
	SELECT product,'Delhi' as City, Delhi as Price from tb_price  
  
 )  
 Merge   table1   tgt using source src  
 on   tgt.product = src.product  and  tgt.city= src.city  
 When matched then  
 Update  
 Set ProductCost=Src.price;