SQL & PL/SQL

Hello Guys, Little head scratcher here (probably just my head mind)

Summary of issue: Update between two tables, trying to use rank, wont work.

I've taken a a few of the interest fields here:-

CREATE TABLE spell_tags (
	 Spell_ID   		varchar2(50) ,
	 service_line_spell     varchar2(25) );

CREATE TABLE ranked_spells(
	spell_ID varchar2(10) ,
	dominant_SSNDS varchar2(25) ,
	service_line varchar2(25) ,
	Rank varchar2(3) NOT NULL ) ;

sample data: -

INSERT INTO RANKED_SPELLS ("SPELL_ID" ,"DOMINANT_SSNDS" ,"SERVICE_LINE" ,"RANK" ) VALUES ('100002' ,'08' ,'SD08o' ,'213' );
INSERT INTO RANKED_SPELLS ("SPELL_ID" ,"DOMINANT_SSNDS" ,"SERVICE_LINE" ,"RANK" ) VALUES ('100002' ,'08' ,'SD08s' ,'210' );
INSERT INTO RANKED_SPELLS ("SPELL_ID" ,"DOMINANT_SSNDS" ,"SERVICE_LINE" ,"RANK" ) VALUES ('100003' ,'08' ,'SD22m' ,'416' );
INSERT INTO RANKED_SPELLS ("SPELL_ID" ,"DOMINANT_SSNDS" ,"SERVICE_LINE" ,"RANK" ) VALUES ('100003' ,'08' ,'SD23x' ,'207' );
INSERT INTO SPELL_TAGS ("SPELL_ID" , "SERVICE_LINE_SPELL"  ) VALUES ('100002' ,'' );
INSERT INTO SPELL_TAGS ("SPELL_ID" , "SERVICE_LINE_SPELL"  ) VALUES ('100003' ,'' );

Data looks like that: -

 Select * from spell_tags;

SPELL_ID                                           SERVICE_LINE_SPELL
-------------------------------------------------- -------------------------
100002
100003

2 rows selected.


 Select * from ranked_spells;


SPELL_ID   DOMINANT_SSNDS            SERVICE_LINE              RANK
---------- ------------------------- ------------------------- ---
100002     08                        SD08o                     213
100002     08                        SD08s                     210
100003     08                        SD22m                     416
100003     08                        SD23x                     207

4 rows selected.

Basically I need to run and Update statement (not plsql) to update the Spell_tags table service_line_spell field with the service_line from the ranked_spells table (lowest rank)

Here are some failed attempts, don't laugh;)

update spell_tags
set service_line_spell = (select max(service_line) from ranked_spells
where spell_tags.spell_id = ranked_spells.spell_ID
group by service_line, rank
having rank = min(rank))
where spell_ID in (select distinct spell_ID from ranked_spells);

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


update speccom.spell_tags
set service_line_spell = (select b.service_line from ranked_spell b
where spell_tags.spell_id = ranked_spells.spell_ID
and ranked_spells.rank = min(ranked_spells.rank) group by service_line, rank)
where exists
(select spell_ID from ranked_spells
where spell_ID in (select distinct spell_ID from ranked_spells);

ERROR at line 4:
ORA-00934: group function is not allowed here

This is the outcome I need: -

 Select * from spell_tags;

SPELL_ID                                           SERVICE_LINE_SPELL
-------------------------------------------------- -------------------------
100002                                             SD08s
100003	                                          SD23x

Any ideas guys?