Basic Oracle SQL Query 1

[SmpTraining]

I am trying to update a field in one table with a value from another table and don't know the syntax.

Table1: PEOPLE
Table2: DATA_IMPORT

tables link on NI_Number field

Want to copy Data_Import.Status field into People.Status field where the NI numbers match.

Can anyone help please? Steve Phillips, Crystal Consultant

 

[jimbopalmer]

update people p set p.status = (select d.status from data_import d where d.ni_number = p.ni_number); I tried to remain child-like, all I acheived was childish.

 

[Naith]

Steve,

Try something like:

update people
set people.statue = data_import.status
where people.ni_number = data_import.ni_number;

Regards,

Naith, Crystal Consultant.

 

[SmpTraining]

Just what I was after Jimbo.

The "select" bit was returning multiple rows so I changed it to a "select max(d_status)" statement and it works perfectly.

Thanks again. Steve Phillips, Crystal Consultant

 

[Turkbear]

Use jimbopalmer's solution..

One caveat: If there is more than one record whose ni_number matches in the data_import table, the insert wil fail with a 'query returns more than 1 row' error..

This method will only work with properly modelled tables..
( One to One, not One to Many )

hth

 

[Turkbear]

Gee, things move fast in this forum..

Using Max may not give you what you want - you may want to remove the duplicates...The Max value might not be the 'correct' one, depending on how the data_import table is populated..