on 04-10-2010 11:15 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
68 | |
26 | |
9 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.