correct UPDATE statement 3

[Nanda]

I need to update my table1 with values in table2. In table1 only those records should be updated that matches the criteria of table2. I do not know what is wrong with the following sql statement...

update test_1
set Score = B.Score,
Reason_cd_1 = B.Reason_cd_1,
Reason_cd_2 = B.Reason_cd_2,
Reason_cd_3 = B.Reason_cd_3,
Reason_cd_4 = B.Reason_cd_4,
Reason_cd_5 = B.Reason_cd_5,
From test_1 A, Test_2 B
where test_1.program_num = test_2.program_num
and test_1.test_yr = test_2.test_yr

any help!!!!
Thanks

 

[karluk]

You don't say what error you are getting, but I bet it's a syntax error - the "from" clause is not valid in an update statement. You probably should be doing a subquery instead. Try something like the following. It should work if you can be sure that the subquery will return a unique row.

update test_1
set (Score,
Reason_cd_1,
Reason_cd_2,
Reason_cd_3,
Reason_cd_4,
Reason_cd_5)
= (select Score,
Reason_cd_1,
Reason_cd_2,
Reason_cd_3,
Reason_cd_4,
Reason_cd_5
from Test_2
where test_1.program_num = test_2.program_num
and test_1.test_yr = test_2.test_yr)

 

[karluk]

Sorry, but I think my previous suggestion won't work if you have rows in test_1 that aren't matched in test_2. You'd better add a where clause to make sure that you are updating only matched rows.

update test_1
set (Score,
Reason_cd_1,
Reason_cd_2,
Reason_cd_3,
Reason_cd_4,
Reason_cd_5)
= (select Score,
Reason_cd_1,
Reason_cd_2,
Reason_cd_3,
Reason_cd_4,
Reason_cd_5
from Test_2
where test_1.program_num = test_2.program_num
and test_1.test_yr = test_2.test_yr)
where (program_num,test_yr) in (select program_num, test_yr from test_2)

 

[rishiu]

Try Doing this By a PL/SQL block.Make one cursor with table1
and other with table2 and passing parameters and Updating
I m sure It will work.
Regards
Rishi u.

 

[Nanda]

Thanks Karluk. Thanks Rishi.
When I run above SQL statement, it is giving me error:
"single-row subquery returns more than one row"

Rishi, I do not know how I can create cursor. and how to pass parameter!!

Let me rephrase what I am trying to do. I have 1-100 records in Test_1 and 1-15 in test_2. I need to update test_1 with records of Test_2 for program_num and test_yr are same. e.g. 1-10. else I want to Insert the rest of non-matching records in test_1.
My second question is, is it possible to do these two tasks in one single SQl statement, or I have to do it in two steps.

Thanks all.

 

[karluk]

The "single-row subquery returns more than one row" error is very common in this type of situation. It means that you have duplicate records in table_2 with the same program_num and test_yr. Oracle needs to find a single matching record in table_2 for every row in table_1. Otherwise it doesn't know which of two or more matching rows to use for the update.

You have several options at this point. If you can, add additional matching criteria to your where clause to make the match unique. If you can't do that you may be able to delete the duplicate rows from table_2. This would be a good option if the duplicates were entered in error. Another possibility is to refine the subquery so that it does return a single row. For example, you may be able to use a group by clause and select max(score) for the update.

Your other question is about inserting non-matching rows from test_2 into test_1. Clearly this will have to be done separately. It is an insert, whereas your other statement is an update. The basic sql command is different. The insert will look something like

insert into test_1
(select * from test_2 where (program_num, test_yr) not in
(select program_num,test_yr from test_1));

 

[Nanda]

Thank again Karluk. your post gave me a direction. I had to update multiple records so, select statement was suppose to give multiple records.

well! I solved it by first deleleting matching records then inserting all non-matching records from table1. which now also includes records to be updated earlier.

Anyway, Thanks again.