Update with join

[Former Member]

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

[Former Member · Accepted solution]

Hi,

as I think, Lars's answer did not really help in your context, perhaps we should think about

correlated subqueries.

This are queries using a subquery / a subselect enclosed in brackets.

But different to the query I saw here in the forum, I would change

update rbqspruefpos set gewichtung =

(select rbarbeitsplanpos.gewichtung

from rbarbeitsplanpos join rbaplposausf on rbarbeitsplanpos.rbarbeitsplanposid = rbaplposausf.rbarbeitsplanposid

join rbqspruefpos on rbqspruefpos.rbaplposausfid = rbaplposausf.rbaplposausfid)

to

update rbqspruefpos set gewichtung =

(select rbarbeitsplanpos.gewichtung

from rbarbeitsplanposjoin rbaplposausf on rbarbeitsplanpos.rbarbeitsplanposid = rbaplposausf.rbarbeitsplanposid

WHERE rbqspruefpos.rbaplposausfid = rbaplposausf.rbaplposausfid)

meaning, that rbaplposausfid out of the row to be updated will be used to prepare the new value for gewichtung.

Is this, what you want?

Elke

[Former Member · Accepted solution]

Hello Elke

update rbqspruefpos set gewichtung =

(select rbarbeitsplanpos.gewichtung

from rbarbeitsplanpos join rbaplposausf on rbarbeitsplanpos.rbarbeitsplanposid = rbaplposausf.rbarbeitsplanposid

WHERE rbqspruefpos.rbaplposausfid = rbaplposausf.rbaplposausfid)

Your suggestion works !

Thank you!

But I had a little problem with your command.

Sometimes the users delete records in rbarbeitsplanpos.

(This is the reason to sync gewichtung from rbarbeitsplanpos into rbqspruefpos. Rbqspruefpos is something like a history file.

When the records in rbqspruefpos are created, there is a correct chain into rbaplposausf and from there into rbarbeitsplanpos.

Later this chain may be brocken.)

So with your command i got some null values for gewichtung and gewichtung in rbqspruefpos is declaired with "not null default 1" = error)

So i have to allow null values for gewichtung in rbqspruefpos, call your command,

update gewichtung with 1 where gewichtung is null in rbqspruefpos and then change gewichtung back to not null.

Nevertheless this looks not very elegantly, it worked and i didn't know a better way to do the job.

Thank you again.

Best regards

Albert

[Former Member]

Hi,

your way of doing it is ok, but maybe the function VALUE may help.

VALUE (x, y) will return the first non-null-value.

So: syntactically

set col = VALUE ( (select ....) , 1 )

could do.

But, I am not sure, if it will do together with correlated subquery in update and in your current version.

But I think it worth trying.

Elke

[lbreddemann]

> 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.

Looks like you're doing it wrong.

Subselects need to be enclosed in brackets.

See [Subquery (subquery)|http://maxdb.sap.com/doc/7_6/60/515f3eca2a11d2a97100a0c9449261/content.htm]

Only scalar subselects (these are the ones that by their very structure can only produce zero or one rows in the result set) can omit the brackets.

The database cannot know that there is only one value in this column - you may try and put a aggregation function round it.

regards,

Lars