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

• Edited by Quentin.Samuelson 4 hours 25 minutes ago

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

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

Hi, Quentin.

I may be you need this ?

create table #tempGB (value_blend float, blend_type Varchar(100),blend_id bigint )

insert into #tempGB
select 15.0 as Value_Blend, 'hard' as blend_type,1 as blend_id

UNION SELECT 65.0, 'medium',  1

UNION SELECT 20.0, 'soft', 1
union select 20.0, 'hard', 2
UNION SELECT 75.0, 'medium',2

UNION SELECT 5.0, 'soft',2
union select 30.0, 'hard', 3
union SELECT 30.0, 'medium', 3
union select 40.0, 'soft', 3

UPDATE BLENDREGRESSION_MASSPROPORTIONS
SET BLENDREGRESSION_MASSPROPORTIONS.WEIGHT_PER = tb.value_blend
FROM BLENDREGRESSION_MASSPROPORTIONS
INNER JOIN #tempGB tb ON BLENDREGRESSION_MASSPROPORTIONS.BLEND = tb.blend_id and  BLENDREGRESSION_MASSPROPORTIONS.ORETYPE = tb.blend_type