Skip to Content
0
Former Member
Apr 10, 2010 at 10:15 AM

Update with join

139 Views

Hello Everybody

I have 3 tables in a maxdb 7.6 database

rbarbeitsplanpos key=rbarbeitsplanposid column gewichtung,...

rbaplposausf key=rbaplposausfid columns rbarbeitsplanposid,...

rbqspruefpos key=rbqspruefposid columns rbaplposausfid,gewichtung,...

Now i want to update gewichtung in rbqspruefpos with the value of gewichtung in the corresponding record in rbarbeitsplanpos.

The way to find the correct value is:

From rbqspruefpos with rbaplposausfid into rbaplposausf

From rbaplposausf with rbarbeitsplanposid to rbarbeitsplanpos

**************

I found this hit:

A simple example of the power of including joins in an Update Statement:

Update Tbl1 set tbl1.field1=tbl2.field2*.10,

from Tbl1 join tbl2 on tbl1.PKfield=tbl2.tbl1FK

This will update all the joined rows in tbl1 to 10% of the values in tbl2.

*********************

I code:

update rbqspruefpos set gewichtung = rbarbeitsplanpos.gewichtung,

from rbqspruefpos

join rbaplposausf on rbqspruefpos.rbaplposausfid = rbaplposausf.rbaplposausfid

join rbarbeitsplanpos on rbarbeitsplanpos.rbarbeitsplanposid = rbaplposausf.rbarbeitsplanposid

General error;-5016 POS(73) Missing delimiter: =

This one works without errors:

update rbqspruefpos set gewichtung = (select rbarbeitsplanpos.gewichtung from rbarbeitsplanpos

join rbaplposausf on rbarbeitsplanpos.rbarbeitsplanposid = rbaplposausf.rbarbeitsplanposid

join rbqspruefpos on rbqspruefpos.rbaplposausfid = rbaplposausf.rbaplposausfid)

but all rbqspruefpos.gewichtung are filled with the same value?

The first value found in rbarbeitsplanpos.

Any help welcomed

Best regards

Albert