Remove Duplicates from LEFT OUTER JOIN

You need to GROUP BY 'S.No' & 'L.KEY'

SELECT S.NO, L.KEY FROM SHOP S LEFT OUTER JOIN LOCATN L ON S.NO = L.SHOPGROUP BY S.NO, L.KEY

EDIT Following the update in your scenario

I think you should be able to do this with a simple sub query (though I haven't tested this against an Oracle database). Something like the following

UPDATE shop sSET divnkey = (SELECT DISTINCT L.KEY FROM LOCATN L WHERE S.NO = L.SHOP)

The above will raise an error in the event of a shop being associated with locations that are in multiple divisions.

If you just want to ignore this possibility and select an arbitrary one in that event you could use

UPDATE shop sSET divnkey = (SELECT MAX(L.KEY) FROM LOCATN L WHERE S.NO = L.SHOP)

I had this problem too but I couldn't use GROUP BY to fix it because I was also returning TEXT type fields. (Same goes for using DISTINCT).

This code gave me duplicates:

select mx.*, case isnull(ty.ty_id,0) when 0 then 'N' else 'Y' end as inuse from master_x mx left outer join thing_y ty on mx.rpt_id = ty.rpt_id

I fixed it by rewriting it thusly:

select mx.*, case when exists (select 1 from thing_y ty where mx.rpt_id = ty.rpt_id) then 'Y' else 'N' end as inusefrom master_x mx 

As you can see I didn't care about the data within the 2nd table (thing_y), just whether there was greater than zero matches on the rpt_id within it. (FYI: rpt_id was also not the primary key on the 1st table, master_x).