写了个sql语句:
update pm_requirementdetail set orderjourid = ( select c.oid from pm_requirement a,pm_requirementdetail b,pm_orderjour c ,pub_department d,pub_material e
where a.billid = b.billid and b.mtrlid = c.mtrlid and a.dept = d.deptid and b.mtrlid = e.mtrlid
and a.creator in ('master1','master2','master3','master4','master5','master6','master7','master8') and a.contractno = c.billno and b.quantity = c.quantity and c.billtype = '6703' and c.quantity = c.requirementaddqty and b.orderjourid <> c.oid)可是提示“单行子查询多于一行”,我知道括号里返回的是多个行,只是想表示我的思路。我连接这三个表查出来结果如下:
OID orderjourid
83673 83671
83667 83623
83675 83621
89688 89612
89699 89632
89689 89665
89700 89765
89698 89566其中右边的列都是错的,我想把左边的列对应的复制到右边,这个是通过上边的括号里的select 语句查出来的(语句中没写orderjourid),来自多个表。我的问题如题,怎么把左边的列赋值到右边啊???.....
update pm_requirementdetail set orderjourid = ( select c.oid from pm_requirement a,pm_requirementdetail b,pm_orderjour c ,pub_department d,pub_material e
where a.billid = b.billid and b.mtrlid = c.mtrlid and a.dept = d.deptid and b.mtrlid = e.mtrlid
and a.creator in ('master1','master2','master3','master4','master5','master6','master7','master8') and a.contractno = c.billno and b.quantity = c.quantity and c.billtype = '6703' and c.quantity = c.requirementaddqty and b.orderjourid <> c.oid)可是提示“单行子查询多于一行”,我知道括号里返回的是多个行,只是想表示我的思路。我连接这三个表查出来结果如下:
OID orderjourid
83673 83671
83667 83623
83675 83621
89688 89612
89699 89632
89689 89665
89700 89765
89698 89566其中右边的列都是错的,我想把左边的列对应的复制到右边,这个是通过上边的括号里的select 语句查出来的(语句中没写orderjourid),来自多个表。我的问题如题,怎么把左边的列赋值到右边啊???.....
where a.billid = b.billid and b.mtrlid = c.mtrlid and a.dept = d.deptid and b.mtrlid = e.mtrlid
and a.creator in ('master1','master2','master3','master4','master5','master6','master7','master8') and a.contractno = c.billno and b.quantity = c.quantity and c.billtype = '6703' and c.quantity = c.requirementaddqty and b.orderjourid <> c.oid
and b.colname=t.colname) 要加个连接条件--你应该是要将要更新的表和右边的查询连接更新,如果没有设定连接条件会返回多行还需要进行存在性判断,否则有些记录会被更新成空值
set t.orderjourid =
( select c.oid
from pm_requirement a,
pm_orderjour c ,
pub_department d,
pub_material e
where a.billid = b.billid
and b.mtrlid = c.mtrlid
and a.dept = d.deptid
and b.mtrlid = e.mtrlid
and a.creator in ('master1','master2','master3','master4','master5','master6','master7','master8')
and a.contractno = c.billno
and b.quantity = c.quantity
and c.billtype = '6703'
and c.quantity = c.requirementaddqty
and b.orderjourid <> c.oid
)
where exists
( select c.oid
from pm_requirement a1,
pm_orderjour c1 ,
pub_department d1,
pub_material e1
where a1.billid = b.billid
and b.mtrlid = c1.mtrlid
and a1.dept = d1.deptid
and b.mtrlid = e1.mtrlid
and a1.creator in ('master1','master2','master3','master4','master5','master6','master7','master8')
and a1.contractno = c1.billno
and b.quantity = c1.quantity
and c1.billtype = '6703'
and c1.quantity = c1.requirementaddqty
and b.orderjourid <> c1.oid
);
set b.orderjourid
83673 83667
83667 83673
83675 89688
89688 83675
89699 89689
89689 89699
89700 89698
89698 89700
不过得到的结果很有特点,两两对换了,这位兄台是否还能改进一下,我估计快成功了。谢谢
update pm_requirementdetail t
set t.orderjourid =
( select c.oid
from pm_requirement a,
pm_requirementdetail b,
pm_orderjour c ,
pub_department d,
pub_material e
where a.billid = b.billid
and b.mtrlid = c.mtrlid
and a.dept = d.deptid
and b.mtrlid = e.mtrlid
and a.creator in ('master1','master2','master3','master4','master5','master6','master7','master8')
and a.contractno = c.billno
and b.quantity = c.quantity
and c.billtype = '6703'
and c.quantity = c.requirementaddqty
and b.orderjourid <> c.oid
and b.orderjourid = t.orderjourid)
)
where exists
( select c.oid
from pm_requirement a1,
pm_requirementdetail b1,
pm_orderjour c1 ,
pub_department d1,
pub_material e1
where a1.billid = b1.billid
and b1.mtrlid = c1.mtrlid
and a1.dept = d1.deptid
and b1.mtrlid = e1.mtrlid
and a1.creator in ('master1','master2','master3','master4','master5','master6','master7','master8')
and a1.contractno = c1.billno
and b1.quantity = c1.quantity
and c1.billtype = '6703'
and c1.quantity = c1.requirementaddqty
and b1.orderjourid <> c1.oid
and b1.orderjourid = t.orderjourid
);
OID orderjourid
83673 83671 更新后,orderjourid应该变成OID相等,那么结果是:
OID orderjourid
83673 83673可是这条记录用更新语句里面那个查询是查不出来的,把<>换成=后也许可以查出,但是确不知道这条记录是否是我们更新成功的记录。所以要监测这种更新变化还需要其他的方式,比如记录rowid来判断。
用一个单独的表先记录下更新目标表的所有记录的rowid和orderjourid,还有C表的OID值。
和更新后的结果比较,通过ROWID连接。这样也许可行。