Update query error 1

[lespaul]

So, I've got what I think is the correct query, but i keep getting the error:
[tt]
Invalid expression.
Column qualifer or table S2 undefined[/tt]

here's the query:
[tt]UPDATE CMPSTAMFE s1 SET s1.chgdscl = s2.chgddsc FROM CMPSTAMF s2
WHERE s1.statut = s2.statut and s1.chgabv =s2.chgabv[/tt]

Here's what I'm trying to fix:
[tt]
CMPSTAMF
STATUT CHGABV CHGDSC
66-1-2 SPEED SPEED 10 - 25 MPH OVER POSTED

CMPSTAMFE
STATUT CHGABV CHGDSCL
66-1-2 SPEED null[/tt]

I need what is in CMPSTAMF.CHGDSC (SPEED 10 - 25MPH etc.) also in CMPSTAMFE.CHGDSCL.

Any idea what I'm doing wrong?

Thanks!

Leslie

Have you met Hardy Heron?

 

[Mercury2]

UPDATE CMPSTAMFE s1 
SET s1.chgdscl = 
( SELECT s2.chgddsc FROM CMPSTAMF s2
WHERE s1.statut = s2.statut AND s1.chgabv = s2.chgabv )

WHERE EXISTS 
( SELECT 1 FROM CMPSTAMF s2
WHERE s1.statut = s2.statut AND s1.chgabv =s2.chgabv )

That'd work !

 

[lespaul]

absolute right, worked like a charm!

thanks!
Leslie