Update table from columns of other table

Punnet Patel 21 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points

    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')  
    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)  
    SELECT * FROM t1  
    SELECT * FROM tb_price  
    ; 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  
    SELECT * FROM t1  
    DROP TABLE t1, tb_price  
    1 person found this answer helpful.
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
      ; 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  
     Set ProductCost=Src.price;  
    0 comments No comments