UPDATE company
SET E_company =
(SELECT cec_ename
FROM (SELECT a.*
FROM cec_company a,
(SELECT MIN(id) mi
FROM cec_company
GROUP BY cec_name) b
WHERE a.id = b.mi) c
WHERE c.cec_name = company.company)根据(cec.name=company)相同
更新company.e_company=cec_company.cec_ename
SET E_company =
(SELECT cec_ename
FROM (SELECT a.*
FROM cec_company a,
(SELECT MIN(id) mi
FROM cec_company
GROUP BY cec_name) b
WHERE a.id = b.mi) c
WHERE c.cec_name = company.company)根据(cec.name=company)相同
更新company.e_company=cec_company.cec_ename
这里SELECT MIN(id) as mi
试试
(SELECT MIN(id) mi
这里SELECT MIN(id) as mi
试试
这个没有区别,只是可能数据量大,然后语句更新超时了而已.
上次我写给你的不是连接的方式吗?你自己要用这种嵌套子查询. 虽然连接也好不了多少,但毕竟要好一点.
你终于来了
我的表有20000条记录
你的意思是不是说建一个新表把子查询出来的表添加到那个表中
然后再用新表更新要更新的表是吗
可不可以加我QQ啊:376021552
你SQL太历害了
update s set f=b.f
from tb f
inner join b
on f.id=b.id
inner join (select ....,min(id) mi from b group by ...) c
on b.id=c.mi我很少上QQ.
MSN:[email protected]
所以 (SELECT cec_ename
FROM (SELECT a.*
FROM cec_company a,
(SELECT MIN(id) mi
FROM cec_company
GROUP BY cec_name) b
WHERE a.id = b.mi) c
这么一大段就等于select distinct cec_name from cec_company
from tb s
inner join b
on s.id=b.id
inner join (select ....,min(id) mi from b group by ...) c
on b.id=c.mi
不好意思我这里是写简单了!我这里只是更新一个字段的话就可以用:
select distinct cec_name from cec_company
但如果是对应更新整条记录的话用distinct就不可以实现我要
看不出min(id)有何意义.因为是按cec_name group因为他上次的贴子里说, 按cec_name和company两表关连,是多对多的.
用 b表里同cec_name 最先出现过的记录的某个字段去更新a表中对应字段.
FROM company inner join (select distinct cec_name from cec_company ) AS B
ON A.company = B.cec_name
===========================================
明白,只是他select 的字段与group的字段相同,所以才会觉得多余.
牛人
结贴了
SET E_company =
(SELECT cec_ename
FROM (SELECT a.*
FROM cec_company a,
(SELECT MIN(id) mi,cec_name
FROM cec_company
GROUP BY cec_name) b
WHERE a.id = b.mi and a.cec_name=b.cec_name) c
WHERE c.cec_name = company.company)
UPDATE company
SET E_company = c.cec_ename,
id= c.id,
………
FROM cec_company c
where c.id = (SELECT MIN(id) mi
FROM cec_company WHERE a.cec_name = company.company
GROUP BY cec_name)
Oracle 与 SQL Server 2000 都支持以下写法
UPDATE company
SET E_company =
(SELECT c.cec_ename
FROM cec_company c where c.id = (SELECT MIN(id) mi
FROM cec_company WHERE a.cec_name = company.company
GROUP BY cec_name)
FROM cec_company WHERE a.cec_name = company.company
改成
FROM cec_company a WHERE a.cec_name = company.company樓主自己測試一下吧
SET E_company =
(SELECT cec_ename
FROM (SELECT a.*
FROM cec_company a,
(SELECT MIN(id) mi
FROM cec_company
GROUP BY cec_name) b
WHERE a.id = b.mi) c) d
WHERE d.cec_name = company.company