SQL Compatibility - Update statement with Subquery

[netprw]

Hello,

New to progress but understand that cant use a subquery in SQL ref an update statement. What I am trying to do is :

UPDATE pub.sales t1
set t1.DESC = (select t2.DESC from pub.product t2 where t2.PRODUCTCODE = t1.PRODUCTCODE )

ie, Look up a value from a related table to update the master (t1).

I only have SQL at my disposal to do this (cant use 4GL) so can anyone advise is theri a way to accomodate this in SQL ?

thanks

 

[RealHeavyDude]

For the record: You don't say anything about the Progress/OpenEdge version you are trying to do this with. This is important information as there are dramatic differences in the SQL capabilities between version. The first version of SQL92 support that was some kind of reasonable was introduced with 9.1d. Compared to what is available in later OpenEdge 10 releases it still sucks.

Heavy Regards, RealHeavyDude.

 

[netprw]

Its Openedge 9.1E.

Need to understand if any "workarounds" are possible if teh subquery select for the update is indeed not possible (ie, a cursor perhaps doing single updates within the main loop ??)


thanks

 

[RealHeavyDude]

As far as I understand, embedding a select into another statement is not supported in Progress V9.

Heavy Regards, RealHeavyDude.

 

[netprw]

Appreciate that, but is theior any other way then in Openedge SQL to do the update a different way ?

ie, some form of "outer loop cursor" that then fires individual update statements inside the loop ?

thanks

 

[RealHeavyDude]

Unfortunately I am not aware of any. The issue is that the Progress/OpenEdge database is not a SQL database in the first place. Over time and released Progress has managed to get it closer but it is still far away from what one expects who is used to work with databases from other vendors. As far as I understand, embedding select statements into another statement is beyond the SQL92 standard. From what I know you can't solve you issue with pure SQL - you would need to do the "surroundings" in another language that either supports ODBC or JDBC.

Heavy Regards, RealHeavyDude.

 

[tamhas]

One alternative is to use ABL!