SQL问题，急！请大家帮帮忙。(100分)

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-30

• #1

我有两张表 TEMP_DB01_DBB01,a2 现在a1表在有1000条记录，两表中的关键字段都是：QKDM,CWDM，我通过关键字段把a2中的数据查询到以后更新到a1中，并且a1中需要更新的记录数等于a2中所查到的记录并且都是多条记录，请问这个Sql 如何写？注意！数据库ORACLE。
以下是我写的，不过不正确。
UPDATE TEMP_DB01_DBB01 SET (QHSXS,YHSXS,RGBCNLBZ,BDRCYSP, LJKFCL,LJKCCL,
LJKFQCL) = (SELECT B.QHSXS,B.YHSXS,B.RGBCNLBZ, DECODE(B.BDRCYSP,0,0
,B.BDRCYSP) AS BDRCYSP,0,0,0 FROM (SELECT QKDM,
QHSXS,YHSXS,RGBCNLBZ,BDRCYSP, LJKFCL,LJKCCL,LJKFQCL,CWDM FROM
TEMP_DB01_DBB02 WHERE NY='200211' AND CWDM<>'0000000000') B)
WHERE (TEMP_DB01_DBB01.QKDM IN (
SELECT DISTINCT QKDM FROM TEMP_DB01_DBB02
WHERE NY='200211' AND CWDM<>'0000000000' ))

 

X

xianguo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-30

• #2

UPDATE TEMP_DB01_DBB01
SET QHSXS = (SELECT QHSXS FROM TEMP_DB01_DBB02
WHERE QKDM=TEMP_DB01_DBB01.QKDM AND CWDM=TEMP_DB01_DBB01.CWDM)
WHERE QKDM EXISTS (SELECT QKDM FROM TEMP_DB01_DBB02
WHERE QKDM=TEMP_DB01_DBB01.QKDM
AND CWDM=TEMP_DB01_DBB01.CWDM)
我觉得用存储过程实现起来比一条SQL语句快

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-30

• #3

我试试！我也想用存储过程呀，只是我们公司规定现在有能用存储过程。

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-30

• #4

我照你的方法是这样写的，提示"无郊的关系运行符"
SQL如下：
UPDATE TEMP_DBB01_DBB01
SET (ZPKJC, PJYZ) = (SELECT B.ZPKJC,(PJYZ/ZPKJC) AS PJYZ FROM
(SELECT QKDM,CWDM,COUNT(*) AS ZPKJC,SUM(YZ) AS PJYZ
FROM TEMP_DBB01_KFC
WHERE YZ>0 AND (JLBM='09' OR JLBM='07' OR JLBM='08')
GROUP BY QKDM,CWDM) B)
WHERE TEMP_DBB01_DBB01 QKDM=B.QKDM AND TEMP_DBB01_DBB01 CWDM=B.CWDM

如果把 ：WHERE TEMP_DBB01_DBB01 QKDM=B.QKDM AND TEMP_DBB01_DBB01 CWDM=B.CWDM
去悼提示："单行查询返回多于一行"。

 

微

微湖水生

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-31

• #5

我在SqlServer上试验成功，你试试以下sql：

Update TEMP_DBB01_DBB01
set ZPKJC=B.ZPKJC,PJYZ=B.PJYZ
from TEMP_DBB01_DBB01 as A,TEMP_DBB01_DBB02 as B
where A.PJYZ=B.PJYZ and A.CWDM=B.CWDM

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-10-31

• #6

这个我试过了，在Oracle下不行。

 

B

bhg007

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-02

• #7

试一下下面的语句,我试过可以的.
UPDATE DB01 SET (QHSXS,YHSXS,RGBCNLBZ,BDRCYSP, LJKFCL,LJKCCL,
LJKFQCL) = (SELECT QHSXS,YHSXS,RGBCNLBZ,DECODE(BDRCYSP,0,0,BDRCYSP) AS BDRCYSP,0,0,0 from db02 WHERE NY='200211' AND CWDM<>'0000000000' and cwdm=db01.cwdm and qkdm=db01.qkdm) WHERE (DB01.QKDM IN (
SELECT DISTINCT QKDM FROM DB02
WHERE NY='200211' AND CWDM<>'0000000000' ))

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-02

• #8

先试试！

 

忍

忍者

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-02

• #9

试试这样写
UPDATE TEMP_DBB01_DBB01 A
SET (ZPKJC, PJYZ) = (SELECT B.ZPKJC,(PJYZ/ZPKJC) AS PJYZ FROM
(SELECT QKDM,CWDM,COUNT(*) AS ZPKJC,SUM(YZ) AS PJYZ
FROM TEMP_DBB01_KFC
WHERE YZ>0 AND (JLBM='09' OR JLBM='07' OR JLBM='08')
GROUP BY QKDM,CWDM) B
WHERE A.QKDM=B.QKDM AND A.CWDM=B.CWDM)

 

L

LinSen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-02

• #10

SQL不能集体赋值，只能单个赋值，Set Column1=X1,Column2=X2...

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-03

• #11

送分，问题解决！多谢大家！

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-03

• #12

为什么给不成分？

 

G

gzbxmcx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-11-04

• #13

多人接受答案了。