假如我有表任务表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怎么做？
- SQL code
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);
这样为什么没用了？
------解决方案--------------------
--应该可以这么写,
--数据库是大小写兼容的,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);
------解决方案--------------------
- SQL code
update emails_1442 tmset tm.category = 'unknown.smtp.com.cn'where not exists (select 1 from EM_MAIL_CATEGORY tc where tc.mail_category_name = tm.category);
------解决方案--------------------
我觉得应该是这样的：
- SQL code
UPDATE emails_1442 tm SET tm.category = 'unknown.smtp.com.cn'WHERE to_lower(tm.category) NOT IN (SELECT to_lower(tc.mail_category_name) FROM EM_MAIL_CATEGORY tc);
------解决方案--------------------
to_lower(tc.mail_category_name)-> lower(tc.mail_category_name)
------解决方案--------------------
类似于此，当然如果是字符串的话，需要注意大小写
- SQL code
update emp4 e set e.deptno = 0 where not exists (select 1 from dept d where e.deptno = d.deptno)