Running recursive Merge in SQL server -

i have 2 tables, card list of cardlines associated it

card (unique: code, key) ---- id | code | key  cardline (fk: cardid, unique: cardid, subkey) ---- id | cardid | value | subkey 

consider data

card ---- id | code | key 1  | c1   | k1 2  | c2   | k1 3  | c3   | k2  cardline ---- id | cardid | value | subkey 1  | c1     | 10    | sk1 2  | c1     | 20    | sk2 3  | c2     | 30    | sk1 4  | c2     | 40    | sk3 5  | c3     | 50    | sk4 

here, in card table, c2 needs converted c1 (as c2 incorrect entry, should have been c1). this, trying write merge query, in, if match, need run sub-query merge cardlines every match. (pseudo-code)

merge card target using (select id, code, key card code = 'c2') on (target.code = 'c1' , target.key = k1) when match -- merge cardlines in similar mananner matched card (subquery?) - -- delete card if no more lines assigned after merge when not match -- update code c2 -> c1 

desired output is

card ---- id | code | key 1  | c1   | k1 3  | c3   | k2  cardline ---- id | cardid | value | subkey 1  | c1     | 40    | sk1   <-- merge of cardline id=1 , id = 3, 3 deleted 2  | c1     | 20    | sk2 4  | c1     | 40    | sk3   <-- no merge required, product code update c1 c2 5  | c3     | 50    | sk4 

am not sql expert (.net), if not right approach, appreciate right direction