1

I am trying to update a table whilst using a join. The task is: Modify the database to show that Helen Partou has now learned to play the tambourine adequately.

Here is my attempt:

update MusicianInstrument
set instrumentName = 'Tambourine',levelOfExpertise = 'Adequate'
from MusicianInstrument  join Musician
on MusicianInstrument.musicianNo = Musician.musicianNo
where musicianName = 'Helen Partou';

However I keep getting an error with the FROM statement.....any help?!

Thanks

Ratchet
  • 49
  • 5

2 Answers2

2

You want to use WHERE EXISTS:

UPDATE MusicianInstrument mi
   SET mi.instrumentName = 'Tambourine'
     , mi.levelOfExpertise = 'Adequate'
 WHERE EXISTS ( SELECT 1 FROM Musician m
                 WHERE m.musicianNo = mi.musicianNo
                   AND m.musicianName = 'Helen Partou');

On a side note since you're using Oracle I would recommend not using CamelCase or headlessCamelCase for object names.

David Faber
  • 12,277
  • 2
  • 29
  • 40
2

Your task is to modify database, so probably not only update table but also insert values if they are not already there. You can do this using merge: 

merge into MusicianInstrument i
using (select * from Musician where musicianName = 'Helen Partou') m
on (m.musicianNo = i.musicianNo 
  and i.instrumentName = 'Tambourine')
when matched then update set levelOfExpertise = 'Adequate'
when not matched then insert (i.musicianNo, i.instrumentName, i.levelOfExpertise)
   values (m.musicianNo, 'Tambourine', 'Adequate')
Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Hey, I tried this method again. However, for instance if Helen did not originally know how to play the instrument Tambourine, the merge query should only insert 1 row, but it is inserting 7 rows. I'm I making any sense? How do I solve this? –  Mar 29 '15 at 18:17
  • 1
    You are right, there was a bug in lines 2-3. Please check now. – Ponder Stibbons Mar 29 '15 at 18:40