gistfile1.txt

SELECT b1.id, b1.code
FROM brands b1 LEFT OUTER JOIN brands b2
  ON (b1.code = b2.code AND b1.id > b2.id)
where b2.id is null



select a.*, (
	SELECT b1.id
		FROM brands b1 LEFT OUTER JOIN brands b2
		ON (
			b1.code = b2.code AND b1.id > b2.id)
			where b2.id is null and UPPER(b1.code) = UPPER(b.code)
	) as foobar
from accounts a
LEFT JOIN brands b on b.id = a.brand_id




UPDATE accounts a LEFT JOIN brands b on b.id = a.brand_id set brand_id = (
	SELECT b1.id
		FROM brands b1 LEFT OUTER JOIN brands b2
		ON (
			b1.code = b2.code AND b1.id > b2.id)
			where b2.id is null and UPPER(b1.code) = UPPER(b.code)
	)

DELETE FROM brands WHERE id NOT IN 
	(SELECT b1.id
		FROM brands b1 
		LEFT OUTER JOIN brands b2 ON (b1.code = b2.code AND b1.id > b2.id)
		where b2.id is null
	)