CODEWITHSUNDEEP
sqloracle-database
user3054588
user3054588

Reputation:

Update query whilst joining two tables?

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

Upvotes: 1

Views: 164

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

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')

Upvotes: 2

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 2

Related Questions