Update query

[Queryman]

I have two tables
1. named "A" has three fields
pat_id (unique integer) and DOS (date field) & DOS2 which
is an empty date field
2. named "B" has two fields(Pat_id & DOS)

I am trying to update the field in table "A" called DOS2 so that it
would contain the date field from table "B"
update a_patient set DOS2 = b_patient.DOS where
a_patient.pat_id in (select pat_id from b_patient)


Even though I have unique pat_id's in both tables and all the pat_id's in table "A" are in table "B", I get a dulpicate row error in table "A" when trying to update.



Michael

 

[rrrkrishnan]

Try this

update a_patient
from b_patient
set dos2=b_patient.dos
where b_patient.pat_id = a_patient.pat_id

 

[Queryman]

Thanks, that worked



Michael

 

[dnoeth]

Did you EXPLAIN it?
The correlation between the two tables is missing so you're trying to update a row with every value from the second table.

update a_patient from b_patient
set DOS2 = b_patient.DOS where
a_patient.pat_id = b_patient.pat_id

The "from b_patient" is optional in this case

Dieter