locked
Update and select from table RRS feed

  • Question

  • I have some data I am getting from a select query (Q1).

    a    27
    b    25
    c    28

    I need to update another set of tables with values returned from the above query (Q1) one at a time and then perform a select

    update t1 set value = 27 (from a above)
    select * from t2 where calcvalue = 9
    update t1 set value = 25 (from b above)
    select * from t2 where calcvalue = 2
    update t1 set value = 28 (from c above)
    select * from t2 where calcvalue = 9

    My question... is this even possible?

    Thanks

    Thursday, April 23, 2015 2:37 AM

Answers

  • Thanks Stan,

    Here is the basic scenarion

    create table #tempGB (hard float, medium float, soft float)
    
    insert into #tempGB
    select 15.0 as hard,
           65.0 as medium,
           20.0 as soft
    union
    select 20.0 as hard,
           75.0 as medium,
           5.0 as soft
    union
    select 30.0 as hard,
           30.0 as medium,
           40.0 as soft
    
    
    -- now I need to update the BLENDREGRESSION_MASSPROPORTIONS table 
    -- with values per row from #tempGB 
    -- and after each row is updated in the table I perform a query based on a table-valued function (METCALC2) which also pases in the paramters of the #tempGB table
    
    -- because each value from tempGB is used to perform internal calulcations within the METCALC2 function, I need to update the tables that the function uses one at a time.
    
    UPDATE BLENDREGRESSION_MASSPROPORTIONS
    SET BLENDREGRESSION_MASSPROPORTIONS.WEIGHT_PER = case	when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'HARD' then tb.hard 
    														when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'MEDIUM' then tb.medium
    														when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'SOFT' then tb.soft
    												end
    FROM BLENDREGRESSION_MASSPROPORTIONS
    INNER JOIN #tempGB tb ON BLENDREGRESSION_MASSPROPORTIONS.BLEND = 'variable';
    
    SELECT		r.* 
    FROM		#tempGB t 
    cross apply	METCALC2( t.hard, t.medium, t.soft) as r

    Thursday, April 23, 2015 3:10 AM

All replies

  • your question is somewhat unclear... you can do that provided you have some columns you can join on.. based what you have provided it is hard to tell ...

    if do not have any columns to join on,  you have to do it hard coding the value...

    please post your question more clear along with DDL's


    Hope it Helps!!

    Thursday, April 23, 2015 2:48 AM
  • Thanks Stan,

    Here is the basic scenarion

    create table #tempGB (hard float, medium float, soft float)
    
    insert into #tempGB
    select 15.0 as hard,
           65.0 as medium,
           20.0 as soft
    union
    select 20.0 as hard,
           75.0 as medium,
           5.0 as soft
    union
    select 30.0 as hard,
           30.0 as medium,
           40.0 as soft
    
    
    -- now I need to update the BLENDREGRESSION_MASSPROPORTIONS table 
    -- with values per row from #tempGB 
    -- and after each row is updated in the table I perform a query based on a table-valued function (METCALC2) which also pases in the paramters of the #tempGB table
    
    -- because each value from tempGB is used to perform internal calulcations within the METCALC2 function, I need to update the tables that the function uses one at a time.
    
    UPDATE BLENDREGRESSION_MASSPROPORTIONS
    SET BLENDREGRESSION_MASSPROPORTIONS.WEIGHT_PER = case	when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'HARD' then tb.hard 
    														when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'MEDIUM' then tb.medium
    														when BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = 'SOFT' then tb.soft
    												end
    FROM BLENDREGRESSION_MASSPROPORTIONS
    INNER JOIN #tempGB tb ON BLENDREGRESSION_MASSPROPORTIONS.BLEND = 'variable';
    
    SELECT		r.* 
    FROM		#tempGB t 
    cross apply	METCALC2( t.hard, t.medium, t.soft) as r

    Thursday, April 23, 2015 3:10 AM