update within a table

[roedelfroe]

Hi,

following problem:

I need to update some rows in a table. All information, which colums are to be updated are in the table itself.

For example, following statement does exactly, what I want:

update basis_table set factor =
(
select factor from basis_table
where type = 'STK' and id_group in ('1','2') and date =
(
select max(date) fro basis_table where id = '1234'
)
and id '1234'
)
where type = 'CER' and id_group in ('511','512') and id = '1234'

As you can see, it works for a GIVEN id. Now I need a statement, which will update all id's whith the 'factor', which is related to this id.

Can anybody help me?

Thx in advance

 

[LKBrwnDBA]

What do you mean by 'related'?

Something like this?:

update basis_table u
   set factor = (
       select factor from basis_table x
        where type = 'STK' 
          and id_group in ('1','2') 
          and date =(
              select max(date) from basis_table
               where x.id = u.id)
          and x.id = u.id )
where type = 'CER' 
  and id_group in ('511','512');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[roedelfroe]

Hi LKBrwnDBA,

thanks for your help.

It was not the solution, but your statement led me to the right idea.

The Update works now with following code:

update basis_table u
set factor = (
select distinct factor from basis_table x
where type = 'STK'
and id_group in ('1','2')
and date =(
select max(date) from basis_table y
where x.id = u.id
and x.id = y.id)
and x.id = u.id )
where type = 'CER'
and id_group in ('511','512');

Thank you again!

roedelfroe