Progress Syntax

[legacysupp]

Hi

I am new to Progress sql and I am having trouble trying to execute a query.

I have 3 tables
A (gtest) - id, newdate
B (contrelations) - con-reference, rel-record
C (contacts) - con-reference, con-created-date

I want to take each record in A and find the corresponding record in C (via B) if one exists and update C's data with the newdate from A

The query I have written is as follows:

update pub.contacts
set "con-created-date" = (select newdate from pub.gtest inner join pub.contrelations on pub.contrelations."rel-record"
= pub.gtest.id where pub.contrelations."con-reference" = pub.contacts."con-reference")

and the error is
=== SQL Exception 1 ===
SQLState=42000
ErrorCode=-20003
[JDBC Progress Driver]:Syntax error (7587)

Can anyone help please!

Adrienne

 

[noddy17]

hi iam new here i was just wondering if anyone had any source code for sql stored procedures it would be a great help if you did thanks

 

[noddy17]

hi i think you might have to create a realtionship between your three tables and this might work i am not to sure

 

[klar]

Can't do a correlated update

I have a similar but simpler update I'm trying to do. The idea is to update a field in table A with the value in table B, where the two tables can be joined on a common field. Here is a sample from among many variations on the theme.
update pub.citationmaster A
set (a.cstatus) = (SELECT COALESCE(B.statuscode, A.cstatus) FROM pub.mtistatus B

where B.ticketno = A.ccitationno)

This gives
=== SQL Exception 1 ===

SQLState=42000

ErrorCode=-20003

[JDBC Progress Driver]:Syntax error (7587)

I've been told by Progress Support that this is a bug in the SQL-92 interpreter, and that they will invenstigate, and either publish a fix or change the documentation (which suggests that this kind up update should work). This is a very common SQL statement structure and works in other databases (e.g. MS SQL Server).

The above statement would (if it worked) update ALL records in table A. To update only those records where there is a corresponding record in table B, the following should work (and does in other databases):

UPDATE pub.CitationMaster

SET

(cStatus) = (SELECT b.StatusCode FROM pub.MTIStatus as b

WHERE cCitationNo = b.TicketNo)

WHERE EXISTS (SELECT * FROM pub.MTIStatus as b2

WHERE cCitationNo = b2.TicketNo)

However, this results in

=== SQL Exception 1 ===

SQLState=HY000

ErrorCode=-20009

[JDBC Progress Driver]:Column ambiguously specified (7523)

Does anybody know of a way to make this kind of correlated update actually work?