0

I am trying to use this query to update a table wherein I am adding a new musician named Helen Partou who has recently learnt how to play the tambourine adequately. Here is the query:

merge into MusicianInstrument i
using Musician m
on (m.musicianNo = i.musicianNo 
and i.instrumentName = 'Tambourine'
and m.musicianName = 'Helen Partou')
when matched then update set levelOfExpertise = 'Adequate'
when not matched then insert (i.musicianNo, i.instrumentName, i.levelOfExpertise)
values (m.musicianNo, 'Tambourine', 'Adequate');

However, this error keeps appearing: An UPDATE or INSERT statement attempted to insert a duplicate key.

There is no existing key as the error may suggest. Here is the original insert into statements with data specific to each musician:

insert into MusicianInstrument(musicianNo,instrumentName,levelOfExpertise)
values('04','Saxophone','Expert');
insert into MusicianInstrument(musicianNo,instrumentName,levelOfExpertise)
values('04','Harp','Average');

Since the MusicianInstrument table does not contain any detail about Helen learning the Tambourine at an Adequate level, the merge into statement should update the table with this information.

Why am I getting this error? The RDBMS I am currently using is Oracle. Any help would be appreciated greatly!

Thanks.

user2736738
  • 30,591
  • 5
  • 42
  • 56
paulmbw
  • 9
  • 3
  • 1
    Don't you need to show us what your table definitions look like and identify what the keys are? – clearlight Mar 29 '15 at 18:49
  • Move Helen Partou to line 2. There is her place. You are probably using answer from this [question](http://stackoverflow.com/questions/29285181/update-query-whilst-joining-two-tables), which was corrected. – Ponder Stibbons Mar 29 '15 at 19:27

0 Answers0