UPDATE - Multiple table problem 1

[DCCoolBreeze]

OK. Can this be done. Let's say we have two tables. A main table (main) and a detail table (detail). They are connected via ID field. Now I want to update some records in the detail table. These records (recordset) is derived from information in the main table. For example:

MAIN ID, fld1, fld2, fld3, ...
DETAIL D_ID, ID, dfld1, dfld2, dfld3, ...

Now I want to update dfld2 for all records that are part of ID, fld1 and fld3 from the main table. How can this be done???

 

[swampBoogie]

update detail set dfld2 = 4711
where exists (select *
from master
where master.id = detail.id
and fld1 = detail.dfld1
and fld3 = detail.dfld3 )

I'm not whether I understand what you mean though. It is often better to give some sample data for the tables involved and the expected result.

 

[DCCoolBreeze]

ok. thanks i will try this

 

[DCCoolBreeze]

I tried it but it did not work...it updated to many records yet the select statement displayed the correct number of records. Let me give an example of what I am looking for.

table1 table2
table1_id table2_id
field11 table1_id
field21

Data (BEFORE)

Table1 table1_id field11
1 ABC
2 DEF

Table2 table2_id table2_id field22
1 1
2 2
3 2
4 2

UPDATE table2
SET table2.field22 = xyz
WHERE table1.field11 = DEF
AND table1.table1_id = table2.table1_id

Data (AFTER)

Table1 table1_id field11
1 ABC
2 DEF

Table2 table2_id table1_id field22
1 1
2 2 xyz
3 2 xyz
4 2 xyz

I am using ORACLE SQL

 

[swampBoogie]

UPDATE table2
SET field22 = 'xyz'
WHERE table1_id in
(select table1.table1_id from table1
where table1.field11 = 'DEF' )