Update table from columns of other table

Punnet Patel 21 Reputation points
2022-10-01T07:53:11.907+00:00

We have a tables with following data

246651-image.png

246635-image.png

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

246644-image.png

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
Transact-SQL
Transact-SQL
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
    2022-10-01T09:00:37.423+00:00

    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  
    
    1 person found this answer helpful.
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2022-10-01T18:18:32.2+00:00
      ; 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;  
    
    0 comments No comments