correct UPDATE statement

[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

 

[Andel]

Hi, try this...

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_2 B
where test_1.program_num = B.program_num
and test_1.test_yr = B.test_yr

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[Nanda]

Thanks Andel.
But oops!! it did not work :-(

 

[Bazzer]

Hi Nanda,

Try this:

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 A.program_num = B.program_num
and A.test_yr = B.test_yr

There are two main differences: the exclusion of the comma and the using the alias.

Otherwise do not define the alias and use:

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 , Test_2 B
where test_1.program_num = B.program_num
and test_1.test_yr = B.test_yr

Regards Bazza