How to update table A from values in table B?

[klar]

I have a "simple" 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 investigate, 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? I'm using 9.1D.

 

[enbw]

Did you get a reply on this?


I really need a solution...

 

[vinod_home]

Hi,

What do you need solution for..

 

[enbw]

You will have to bear with me as I am new to SQL.

I have two tables. One table,partopr, contains operation details. The other table, partmtl, contains materials related to the operation.
I have the following select query , this works.

select distinct pub.partopr.partnum, pub.partmtl.revisionnum, pub.partmtl.mtlpartnum, pub.partmtl.qtyper, pub.partopr.prodstandard
from pub.partmtl, pub.partopr
where
pub.partmtl.mtlpartnum like '%MELT%' and
pub.partmtl.partnum = pub.partopr.partnum
and pub.partmtl.revisionnum = pub.partopr.revisionnum
and pub.partmtl.relatedoperation = pub.partopr.oprseq
and pub.partmtl.qtyper<> pub.partopr.prodstandard / 100

Basically I want to update the prodstandard field in pubopr with partmtl.qtyper * 100 if pubopr.prodstandard field is not equal to partmtl.qtyper / 100 and the other conditions above.

I have tried using an UPDate syntax but it keeps throwing up an systax error.

It maybe just me and my inexperience.

Thanks in advance

 

[jai shankar]

Reply for Query

hi,
First I want to know that on which u are working.
if this is oracle based sql then try update command.
or if this is progress sql92 then try using assign statement.
thanks.