Hi. On Mon, Dec 03, 2001 at 06:24:14PM -0500, [EMAIL PROTECTED] wrote: > > I try to do an Oralce query in mySQL > > insert into geoTable select '011852','HK' from dual where not exists > (select * from geoTable where geoID = '011852') > > In Oracle, this query will check the new geoID is not exist before > inserting into the table. > > In mySQL, I do > > create temporary table tmp1 select '011852' geoID, 'HK' countryName; > create temporary table tmp2 select a.geoID, a.countryName from tmp1 a > left join geoTable b on a.geoID = b.geoID where a.geoID is null; > insert into geoTable select * from tmp2; > > > Do anyone know mySQL has a similar function like Oracle to check "NOT > EXISTS" or there is a better queries than what I use here.
Yes, use a LEFT JOIN and check for NOT NULL in the result. This is even covered by the fine manual: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html Btw, it is the forth hit if you feed the search engine with simply "not exists" (the way I got the URL). Bye, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
