locked
Need Update Query Based On My Scenrio RRS feed

  • Question

  • User2033107836 posted
    Hello
    I have one table1 in that i have 7 columns
    lets take 4 columns 
    RefNo,
    Type,
    ItemName,
    Qty
    
    My Result shows like this
    RefNo	Type	ItemName	  New_Qty
    A1	Fruits	Apple	          1.5
    A1	Fruits	Mango	          2.003
    A1	Fruits	Banana             4
    
    My Table1 have data like this
    RefNo	Type	ItemName	  Qty
    A1	Fruits	Apple	   
    A1	Fruits	Mango	   
    A1	Fruits	Banana          
    
    I want My Resultset Qty to be update on Table1 Qty  based where clause on RefNo Type ItemName
    need update query 
    

    Thursday, September 7, 2017 7:47 AM

Answers

  • User1970566204 posted

    hi

    update query as below

     update  @MyTable1 set Qty=B.New_Qty
     from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
    

    Full part as below

    declare @MyResult table(RefNo varchar(5),Type varchar(10),ItemName varchar(10),New_Qty float)
    declare @MyTable1 table(RefNo varchar(5),Type varchar(10),ItemName varchar(10),Qty float)
    
    insert into @MyResult values('A1','Fruits','Apple',1.5)
    insert into @MyResult values('A1','Fruits','Mango',2.003)
    insert into @MyResult values('A1','Fruits','Banana',4)
    
    insert into @MyTable1 values('A1','Fruits','Apple',0)
    insert into @MyTable1 values('A1','Fruits','Mango',0)
    insert into @MyTable1 values('A1','Fruits','Banana',0)
    
    select * from @MyResult
    select * from @MyTable1
    select * from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
    
     update  @MyTable1 set Qty=B.New_Qty
     from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
     
    select * from @MyTable1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 7, 2017 9:00 AM
  • User347430248 posted

    Hi asp.ambur,

    I try to create a table like yours.

    Query:

    update fruit_data 
    set qty = (CASE 
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Apple' 
                            THEN '1.5'
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Mango' 
                            THEN '2.003'
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Banana' 
                            THEN '4'
                        END);

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 7, 2017 9:01 AM

All replies

  • User1970566204 posted

    hi

    update query as below

     update  @MyTable1 set Qty=B.New_Qty
     from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
    

    Full part as below

    declare @MyResult table(RefNo varchar(5),Type varchar(10),ItemName varchar(10),New_Qty float)
    declare @MyTable1 table(RefNo varchar(5),Type varchar(10),ItemName varchar(10),Qty float)
    
    insert into @MyResult values('A1','Fruits','Apple',1.5)
    insert into @MyResult values('A1','Fruits','Mango',2.003)
    insert into @MyResult values('A1','Fruits','Banana',4)
    
    insert into @MyTable1 values('A1','Fruits','Apple',0)
    insert into @MyTable1 values('A1','Fruits','Mango',0)
    insert into @MyTable1 values('A1','Fruits','Banana',0)
    
    select * from @MyResult
    select * from @MyTable1
    select * from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
    
     update  @MyTable1 set Qty=B.New_Qty
     from @MyTable1 A inner join  @MyResult B on A.ItemName=B.ItemName and A.RefNo=B.RefNo and A.Type=B.Type
     
    select * from @MyTable1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 7, 2017 9:00 AM
  • User347430248 posted

    Hi asp.ambur,

    I try to create a table like yours.

    Query:

    update fruit_data 
    set qty = (CASE 
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Apple' 
                            THEN '1.5'
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Mango' 
                            THEN '2.003'
                          WHEN ref_no = 'A1' and type='Fruit' and item_name='Banana' 
                            THEN '4'
                        END);

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 7, 2017 9:01 AM