CTE query or SQL query

Transact-SQL
, ,
1

3384-problem1.png357×823 22.3 KB

I have table1 which contains 3 columns---> Product | company |
Demand...the demand is not present in table1......in table2 i have 2
columns--> demand | company which contains demand .....i need to
update table1 info in such way that all the related products get the
demand if anyone company matches in 2 tables

2

Hi,

I have an idea: after your update, you could execute another update , the join , in this case will be base on Product (we could make all in one update statement)

DECLARE @table1 TABLE
(  Product VARCHAR(20) NOT NULL,
   Company VARCHAR(20) NOT NULL,
   Demand INT  NULL )
DECLARE @table2 TABLE
(  Demand INT NOT NULL,
   Company VARCHAR(20) NOT NULL)


INSERT INTO @table1(Product,Company)
VALUES ('Bike','Honda'),
      ('Bike','Suzuky'),
      ('Bike','Hero'),
      ('Bike','Ktm'),
      ('Car','nissan'),
      ('Car','renault')

INSERT INTO @table2(Demand,Company)
VALUES  (25,'Ktm'),
        (35,'davidson'),
        (59,'nissan')


UPDATE    A
SET A.Demand = B.Demand
FROM
    @table1 AS A
    INNER JOIN @table2 AS B
        ON A.Company = B.Company

--SELECT * FROM @table1

UPDATE    A
SET A.Demand = CA.Demand
FROM
    @table1 AS A
    CROSS APPLY
    ( SELECT TOP(1)
        T1.Demand
      FROM 
        @table1 AS T1        
      WHERE
        T1.Demand IS NOT NULL
        AND T1.Product = A.Product
    )CA(Demand)
WHERE
    A.Demand IS NULL

SELECT * FROM @table1

The output:

Product              Company              Demand
Bike                 Honda                25
Bike                 Suzuky               25
Bike                 Hero                 25
Bike                 Ktm                  25
Car                  nissan               59
Car                  renault              59
1 Like
3

thank you very much :smile:

4

Welcome :smiley: