mysql row priority

Question

I have a database table full of some really ugly and messy data. In a seperate table i have a cleaner version of the data and they are linked by an id, but I need to keep the messy dataset and can't overwrite it as I use it to check against data differences.

I'm trying to merge the data into a new table, OR use a single query across both tables and give the clean table results priority in the result.

So if id=3 uglydata=x7z cleandata=xyz, then I'd get the clean data, if cleandata was null,i'd get the ugly data. I tried selecting cleandata AS uglydata, hoping that mysql would just overwrite the other field, but that doesn't work (and yes, it's weird and I figured that wouldn't work).

Is their a nice way to do this? The other solution I can think if is just to insert into the new table from the clean data first, and then insert from the ugly data as the bid is unique.

But i'm hoping i'll be able to prioritize results by type or something.

Thanks Pete

Answer 1

SELECT IFNULL(cleandata, uglydata)
FROM uglytable
LEFT OUTER JOIN cleantable
ON clean_id = ugly_id