关于sql语法的问题

bbniu

需求：
表a 有字段 a1,p1,p2
表b 有字段 p1,p2

现在把表a中p1，p2等于表b中p1，p2的a1值改为1

请问此sql语句如何写效率最高

谢谢！

del0723

update  (select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2) as table1
set table1.a1 = 1

bbniu

经验证，不成功
语法错误

pantierui

UPDATE TABLE1 SET TABLE1.A1 = 1                                                                        
WHERE
(TABLE1.P2 = (SELECT P2 FROM  TABLE2 WHERE TABLE1.P2=TABLE2.P2))               
AND   
(TABLE1.P1 = (SELECT P1 FROM TABLE2 WHERE TABLE1.P1=TABLE2.P1))              
不知道这样能不能实现你的要求.但效率不是很好.

zxalh..

update a set a1=1 where a1 in (select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2)
效率怎么样不清楚了

pantierui

原帖由 zxalh.. 于 2007-3-3 10:22 发表
update a set a1=1 where a1 in (select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2)
效率怎么样不清楚了

受教了.

lklgdkp

with c as
(select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2)
update c set a1=1

A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

[ 本帖最后由 lklgdkp 于 2007-3-7 23:10 编辑 ]

beginner-bj

原帖由 lklgdkp 于 2007-3-7 22:51 发表
with c as
(select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2)
update c set a1=1

A common table expression can be thought of as a temporary result set that is defined within the execution sc ...

这个写法比较好理解

pjj0618

执行
with c as
(select a1 from a,b where a.p1 = b.p1 and a.p2 = b.p2)
update c set a1=1
会报错呀？大家指点一二吧。

raullew

原帖由 beginner-bj 于 2007-3-8 09:56 发表

这个写法比较好理解

恩，这样写才叫update呀