Update table after join

[Former Member]

Hello Dear Experts

I have made the program with simple join:

REPORT XXX.

Tables: AAA, BBB, CCC, DDD.

select single

b~field2

c~field3

a~field1

into (DDD-field4, DDD-field5, DDD-field6)

from AAA AS a

INNER JOIN BBB AS b ON a_{field1 = b}field2

AND a_{fieldx = b}fieldx

INNER JOIN CCC AS c ON a_{field1 = c}field1

AND a_{fieldx = c}fieldx

WHERE a~fieldx = 'A'.

update CCC from CCC.

commit work.

The question is, how can I update the table CCC after the join is made?

Regards

Robert

[Former Member]

Hi,

you try to update CCC with CCC. How should this change somethink??

You need to update CCC from an other workarea.

Regards

Nicole

[Former Member]

Sorry, It was small mistake in the explanation : should be update DDD from ddd....

Regards

Robert

[Former Member]

Create a Maintenance View in SE11 using the condition specified in the select query.

Use that View to update.

[Former Member]

Hi Robert,

Use it like this.

Update ccc from table CCC.

Much Regards,

Amuktha.

[Former Member]

Other Explanation of the problem:

REPORT XXX.

Tables: AAA, BBB, CCC, DDD.

select single

b~field2

c~field3

a~field1

into (DDD-field4, DDD-field5, DDD-field6)

from AAA AS a

INNER JOIN BBB AS b ON a_{field1 = b}field2

AND a_{fieldx = b}fieldx

INNER JOIN CCC AS c ON a_{field1 = c}field1

AND a_{fieldx = c}fieldx

WHERE a~fieldx = 'A'.

How to update DDD Table in the database (I don't want to use view, the example is made directly on database tables without creating internal tables)?

[naveen_inuganti2]

Better way is,

Create the work area to save three variables of DDD and then Update the DDD table.

__Naveen Inuganti.

[Former Member]

Tables: AAA, BBB, CCC, DDD.

data: idata TYPE STANDARD TABLE OF ddd

data: wa_data TYPE ddd.

select

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field1 = b}field2

AND a_{fieldx = b}fieldx

INNER JOIN CCC AS c ON a_{field1 = c}field1

AND a_{fieldx = c}fieldx

WHERE a~fieldx = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update ddd from wa_data.

endloop.

Edited by: Nicole Lorenz on Feb 26, 2009 6:22 AM

Edited by: Nicole Lorenz on Feb 26, 2009 6:23 AM

[former_member222860]

Hi,

Pl.check, if it works:

Tables: AAA, BBB, CCC, DDD.

select single
b~field2
c~field3
a~field1
into (DDD-field4, DDD-field5, DDD-field6)
from AAA AS a
INNER JOIN BBB AS b ON a~field1 = b~field2
AND a~fieldx = b~fieldx
INNER JOIN CCC AS c ON a~field1 = c~field1
AND a~fieldx = c~fieldx
WHERE a~fieldx = 'A'.

UPDATE DDD SET: field4 = ddd-field4,
                field5 = ddd-field5,
                field6 = ddd-field6.

thanks\

Mahesh

[Former Member]

Hi

I've created the select:

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data TYPE DDD.

select

b~field1

c~field2

a~field3

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

I've got the error: you cannot use an internal table as a work area. What is the reason for this problem?

Robert

[Former Member]

Data: wa_data TYPE DDD.

is the mistake.

It should be:

wa_data like line of idata.

Regards,

Bobi

[Former Member]

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data like line of idata.

select

b~field1

c~field2

a~field3

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

The same error: You cannot use an internal table as work area.

Robert

[Former Member]

Please use code tags. Try this:

SELECT b~field1 c~field2 a~field3
  INTO wa_data                          <====
  FROM aaa AS a
    INNER JOIN bbb AS b ON a~field4 = b~field1
                       AND a~field3 = b~field5
    INNER JOIN ccc AS c ON a~field6 = c~field2
                       AND a~field3 = c~field7
  WHERE a~field3 = 'A'.
  COLLECT idata.
ENDSELECT.

Rob

[Former Member]

Hi,

use the statements below to compare the mistake you have done in coding

Tables: BBB, CCC, AAA, DDD.
Data: idata TYPE STANDARD TABLE OF DDD.
Data: wa_data like line of idata.

select
b~field1
c~field2
a~field3
"into idata                           idata is declared as internal table of DDD without header line. so this 
" created a problem because you have not entered into table addition. Instead of this statement use the 
" statement given below
into wa_data
from AAA AS a
INNER JOIN BBB AS b ON a~field4 = b~field1
AND a~field3 = b~field5
INNER JOIN CCC AS c ON a~field6 = c~field2
AND a~field3 = c~field7
WHERE a~field3 = 'A'.
collect wa_data to idata.                       " also change this statement from collect idata to the given 
" statement
endselect.

loop at idata into wa_data.
update DDD from wa_data.
endloop.

Regards,

Siddarth

[Former Member]

Thank You Siddarth

I have modified the select:

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data like line of idata.

select

b~field1

c~field2

a~field3

into wa_data

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect wa_data into idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

I don't have any syntax error but update doesn't work. it works for this select (without using int tables and wa):

Tables: BBB, CCC, AAA, DDD.

select

b~field1

c~field2

a~field3

into (DDD-field, DDD-field, DDD-field)

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

update DDD.

endselect.

Regards

Robert

[Former Member]

Hi Robert,

firstly please check the key fields of table DDD and also check if wa is changing the value of the key fields in this case update will not work,

if still you didn't understand pls. paste the key fields of DDD table and one example value of the table

and also the fields and value of wa.

Regards,

Siddarth

[Former Member]

Thank You Siddarth

The both selects work now.

Regards

Robert