假如我有表任务表Task里面有supplierName字段,现在我要查询供应商表里面的供应商,加入供应商里面是126.com,sina.com,163.com,qq.com,sohu.com,yahoo.com.cn,如果我task里面的supplierName里面的供应商在供应商表里面没有126.com,sina.com,163.com,qq.com,sohu.com,yahoo.com.cn这些那么我就全部改成qq.com,这个用sql怎么做?update
emails_1442 tm set tm.category = 'unknown.smtp.com.cn'
where lower(trim(nvl(nvl tm.category,'unknown.smtp.com.cn')))
not in (select tc.mail_category_name from EM_MAIL_CATEGORY tc); 这样为什么没用了?
emails_1442 tm set tm.category = 'unknown.smtp.com.cn'
where lower(trim(nvl(nvl tm.category,'unknown.smtp.com.cn')))
not in (select tc.mail_category_name from EM_MAIL_CATEGORY tc); 这样为什么没用了?
--数据库是大小写兼容的,select sysdate ,SYSDATE from dual得到的结果一致
update emails_1442 tm
set tm.category = 'unknown.smtp.com.cn'
where not exists (select 1 from EM_MAIL_CATEGORY tc);
update emails_1442 tm
set tm.category = 'unknown.smtp.com.cn'
where not exists (select 1 from EM_MAIL_CATEGORY tc where tc.mail_category_name = tm.category);
WHERE to_lower(tm.category) NOT IN
(SELECT to_lower(tc.mail_category_name) FROM EM_MAIL_CATEGORY tc);
可以直接用to_lower?为什么报to_lower无效?
WHERE LOWER(tm.category) NOT IN
(SELECT LOWER(tc.mail_category_name) FROM EM_MAIL_CATEGORY tc);