请教一SQL语句优化

[sh98_wgf]

有IP_ONU 和SYNC_BRAS_User两张表，现在要用Sync_BRAS_USER更新IP_ONU，条件下：
1. 两张表的MAC相同但IP不相同
2. MAC在各自表内没有重复
我写了如下语句，不知道对不好，感觉不是很好，求指导

UPDATE IP_ONU SET IP=B.IP,BINIP=B.BINIP FROM SYNC_BRAS_USER AS B INNER JOIN IP_ONU AS A ON (A.MAC=B.MAC AND A.IP<>B.IP) WHERE ( A.MAC IN ( SELECT MAC FROM IP_ONU GROUP BY MAC Having Count(*)=1 ) AND B.MAC IN ( SELECT MAC FROM Sync_Bras_User GROUP BY MAC Having Count(*)=1 ) );

[筱筱澄]

--try
UPDATE IP_ONU SET IP=B.IP,BINIP=B.BINIP 
FROM SYNC_BRAS_USER AS B 
INNER JOIN IP_ONU AS A 
ON (A.MAC=B.MAC AND A.IP<>B.IP) 
join  ( SELECT MAC FROM IP_ONU GROUP BY MAC Having Count(*)=1 )  c
on a.mac=c.mac
join ( SELECT MAC FROM Sync_Bras_User GROUP BY MAC Having Count(*)=1 ) d
on b.mac=d.mac

[sh98_wgf]

能解释一下这两个语句的区别吗

[黄_瓜]

[Quote=引用 2 楼 的回复:]

能解释一下这两个语句的区别吗
[/Quote]
就死把in 改成了join 这样可能会好一些，也可能两者的结果是一样的。
你可以测试一下

另外需要在快的话就是要有一定的索引，索引可以利于查找满足更新条件的速度。

[luckings]

UPDATE IP_ONU SET IP=B.IP,BINIP=B.BINIP 
FROM (SELECT MAC,MAX(IP) AS IP,MAX(BINIP) AS BINIP FROM Sync_Bras_User GROUP BY MAC Having 
Count(*)=1 ) AS B 
INNER JOIN IP_ONU AS A 
ON (A.MAC=B.MAC AND A.IP<>B.IP) inner 
join  ( SELECT MAC FROM IP_ONU GROUP BY MAC Having Count(*)=1 )  c
on a.mac=c.mac

[撩天槌]

UPDATE IP_ONU
SET IP=B.IP,BINIP=B.BINIP
FROM SYNC_BRAS_USER AS B
INNER JOIN IP_ONU AS A ON (A.MAC=B.MAC AND A.IP<>B.IP)
WHERE (select count(*) from IP_ONU where mac=a.mac)=1
and (select count(*) from Sync_Bras_User where mac=a.mac)=1

[白天的猫头鹰]

在sql中，in关键字是逐条扫描的，即使找到了，也会扫描到完为止，因此in在大数据量的时候效率低下，从sql优化角度来说是不用in，可以使用exists替换，exists只要找到了就直接返回，而不是继续查找下去，效率上会高很多