Okay, this one works fine. Thank you.

Now the tricky part

test is not directly availabel

All i now is a var just like

lcCur = 'Test'

lcCur = 'Test'
UPDATE (lcCur) SET n1 = tm.n1m ;
	FROM (lcCur) inner JOIN ( ;
		SELECT t1.m1, t1.m2, MAX(t1.n1 * t2.f1) AS n1m ;
			FROM (lcCur) t1 inner JOIN Test2 t2 ON t2.Mx = t1.M1 OR t2.Mx = t1.M2 ;
			GROUP BY 1,2 ;
		) tm ON tm.m1 = &lcCur..m1 AND tm.m2 = &lcCur..m2

will not work.

Agnes

>Agnes,
>
>Try
>

>UPDATE Test SET n1 = tm.n1m ;
>	FROM Test JOIN ( ;
>		SELECT t1.m1, t1.m2, MAX(t1.n1 * t2.f1) AS n1m ;
>			FROM Test t1 JOIN Test2 t2 ON t2.Mx = t1.M1 OR t2.Mx = t1.M2 ;
>			GROUP BY 1,2 ;
>		) tm ON tm.m1 = Test.m1 AND tm.m2 = Test.m2	
>
>* or
>
>UPDATE Test SET n1 = Test.n1 * tm.f1m ;
>	FROM Test JOIN ( ;
>		SELECT t1.m1, t1.m2, MAX(t2.f1) AS f1m ;
>			FROM Test t1 JOIN Test2 t2 ON t2.Mx = t1.M1 OR t2.Mx = t1.M2 ;
>			GROUP BY 1,2 ;
>		) tm ON tm.m1 = Test.m1 AND tm.m2 = Test.m2	
>
>
>

>
>>
>>again, I have a SQL Problem. This time it's an UPDATE:
>>
>>given
>>

>>CREATE CURSOR Test(M1 c(1), M2 c(1),n1 I)
>>INSERT INTO Test Values('A','A',1.1)
>>INSERT INTO Test Values('A','B',1.2)
>>INSERT INTO Test Values('B','B',1.3)
>>INSERT INTO Test Values('A','C',1.4)
>>INSERT INTO Test Values('C','B',1.5)
>>INSERT INTO Test Values('C','A',1.6)
>>
>>
>>CREATE CURSOR Test2(Mx c(1),F1 I)
>>INSERT INTO Test2 Values('A',1)
>>INSERT INTO Test2 Values('B',4)
>>INSERT INTO Test2 Values('C',2)
>>

>>
>>I like to update Test.n1 with itself*Test2.F1,
>>
>>Now the problem. F1 is related via Mx to M1 and M2. if M1 !== M2 it should use the higher value of F1
>>Example
>>

>>INSERT INTO Test Values('A','A',1.1) && -> n1:= 1.1  : F1(A)=F1(A)=1
>>INSERT INTO Test Values('A','B',1.2) && -> n1:= 4.2  : F1(A)<F1(B); F1(B)=4
>>INSERT INTO Test Values('C','B',1.5) && -> n1:= 6.0  : F1(C)<F1(B); F1(B)=4
>>INSERT INTO Test Values('C','A',1.6) && -> n1:= 3.2  : F1(C)>F1(>); F1(C)=2
>>

>>
>>any idea how to code this in one UPDATE statement?
>>
>>TIA
>>Agnes