谁能告诉我下在数据库中如何修改最大单据号

lqbadboy1

谁能告诉我下在数据库中如何修改最大单据号，求大神们帮忙

cuicx1987

支持网站有工具啊，知识库里也有sql命令

lqbadboy1

cuicx1987 发表于 2012-8-24 15:39

                               
登录/注册后可看大图

支持网站有工具啊，知识库里也有sql命令

关键是用了也不行啊

cuicx1987

lqbadboy1 发表于 2012-8-24 15:46

                               
登录/注册后可看大图

关键是用了也不行啊

工具不行吗？有没有自己重新一条一条的执行sql

深夜地下铁

在平常处理的问题中，经常会碰到诸如：单据保存时提示“违反了primary key约束'****'不能在对象'***'中插入重复键。新增单据保存失败”的问题。
分析这种错误主要有两个原因：
1、系统库ufsystem中ua_identity表存在重复的记录；
2、Ufsystem中ua_identity表的ifather、ichildid值小于data库中相关联主子表的最大id值。
那么对于第一种情况，解决办法很简单，删除多余的记录。但是该删哪一条呢？我们必须找到对应单据的最大ID，才可判断哪一条是多余的；
第二种情况，新增的单据在data库中是什么表？对应的在系统库中ua_identity表中字段cvouchtype是什么？相对应Id又是什么呢？
通过做新的单据找规律，下面我总结出了各种业务单据在系统库和data库中相关联的表和字段。
注：对应单据名称为我们新增单据的名称，cvouchtype为系统库中ua_identity表对应单据类型字段，ua_identity表中ifatherid对应关联表主表中ID标识， ichildid对应关联表子表中ID标识。
这样我们从cvouchtype判断是什么业务单据，然后到data库中找到相对应单据的主子表，找到其最大的俩ID，与ua_identity表中ifatherid、ichildid比较，保证ifatherid、ichildid大于等于关联data库中关键ID值即可。

对应单据名称	cvouchtype	data库中相关联主子表	相关联主子表ID字段标识
采购请购单	PuApp	PU_AppVouch/s	ID/Autoid
采购订单	Pomain	PO_Pomain/PO_Podetails	POID/ID
采购到货单	PuArrival	PU_ArrivalVouch/s	ID/autoid
采购入库单	rd	Rdrecord/s	ID/Autoid
采购发票	Purbill	PurBillVouch/s	PBVID/ID
销售报价单	QuoMain	SA_QuoMain/ SA_QuoDetails	ID/Autoid
销售订单	Somain	SO_SOMain/SO_SODetails	ID/isosid
销售发（退）货单	Dispatch	DispatchList/s	DLID/idlsid
销售发票	Billvouch	SaleBillVouch/s	SBVID/Autoid
委托代销发货（退）单	Dispatch	DispatchList/s	DLID/Autoid
委托代销结算单	SettleVouch	SA_SettleVouch/s	ID/Autoid
委托代销调整单	Dispatch	DispatchList/s	DLID/Autoid
销售调拨单（红、蓝）	Billvouch	SaleBillVouch/s	SBVID/Autoid
销售零售日报（红、蓝）	Billvouch	SaleBillVouch/s	SBVID/Autoid
代垫费用单	Expense	ExpenseVouch/s	ID/Autoid
销售支出单	Salepay	SalePayVouch/s	ID/autoid
委外发票	Wwpurbill	OM_PurBillVouch/s	PBVID/ID
产成品入库单	rd	Rdrecord/s	ID/Autoid
其他入库单	rd	Rdrecord/s	ID/Autoid
材料出库单	rd	Rdrecord/s	ID/Autoid
其他出库单	rd	Rdrecord/s	ID/Autoid
调拨单	td	TransVouch/s	ID/Autoid
盘点单	ch	CheckVouch/s	ID/autoid
限额领料单	ma	MatchVouch/s	ID/autoid
不合格品记录单	sc	ScrapVouch/ScrapVouchs	ID/Autoid
不合格品处理单	sc	ScrapVouch/ScrapVouchs	ID/Autoid
货位调整单	ad	AdjustPVouch/s	ID/Autoid
应收应付单	sk	ap_closebill/s	IID/ID

举一个实例，问题现象如下图：

我们查询关联的rdrecord/s表中记录，找其最大ID值，参考语句如下：
use UFDATA_883_2008
select max(autoid) from rdrecords
select max(id) from rdrecord
这时我们再看系统库中identity表中记录：
use ufsystem
select * from ua_identity where cacc_id='883' and cvouchtype='rd'
我们发现系统库中ifatherid和ichildid小于data库中的，参考以下语句进行调整即可：
update ua_identity set ifatherid=(select max(id) from ufdata_999_2012..rdrecord) , ichildid=(select max(autoid) from ufdata_999_2012..rdrecords )where cacc_id='999' and cvouchtype='rd'
其中UFDATA_883_2008为问题帐套数据库名称。883为帐套号

pengfei598

楼上正解、支持

lqbadboy1

深夜地下铁 发表于 2012-8-24 16:24

                               
登录/注册后可看大图

在平常处理的问题中，经常会碰到诸如：单据保存时提示“违反了primary key约束'****'不能在对象'***'中插入 ...

系统库ufsystem中ua_identity表 这个表在哪里找，我怎么没有找到

深夜地下铁

lqbadboy1 发表于 2012-8-24 16:58

                               
登录/注册后可看大图

系统库ufsystem中ua_identity表 这个表在哪里找，我怎么没有找到

ufsystem..ua_identity 字段标识描述主表字段子表字段
Rd收发记录主子表Rdrecord.idRdrecords.autoid
BILLVOUCH销售发票主子表SaleBillVouch.SBVID SaleBillVouchs.autoid
DISPATCH发货退货单主子表DispatchList.dlidDispatchLists.idlsid
POMain采购订单主子表PO_Pomain.POIDPO_Podetails.id
SOMain销售订单主子表SO_SOMain.idSO_SODetails.autoid
PURBILL采购发票主子表PurBillVouch.PBVIDPurBillVouchs.ID
PURSTID采购结算单主子表PurSettleVouch.PSVIDPurSettleVouchs.ID
SETTLEVOUCH委托代销结算单主子表SA_SettleVouch.IDSA_SettleVouchs.Autoid
Ju出入库调整单主子表JustInVouch.IDJustInVouch.Autoid
PUAPP采购请购单主子表PU_AppVouch.IDPU_AppVouchs.Autoid
PUARRIVAL采购到货单主子表PU_ArrivalVouch.IDPU_ArrivalVouchs.Autoid
Tr库存调拨单主子表TransVouch.IdTransVouchs.Autoid
EXPENSE代垫费用单主子表ExpenseVouch.IDExpenseVouchs.Autoid
Ch盘点单主子表CheckVouch.ID CheckVouchs.Autoid
Ma限额领料单主子表MatchVouch.IDMatchVouchs.Autoid
SALEPAY销售支出单主子表SalePayVouch.IDSalePayVouchs.Autoid
QUOMAIN销售报价单主子表SA_QuoMain.IDSA_QuoDetails.Autoid
XJ收付款单主子表(销售现结使用)Ap_CloseBill.ccancelnoAp_CloseBills.ID
SK收付款单主子表(收款cCancelNo is null)Ap_CloseBill.iIDAp_CloseBills.ID
OM_MO委外订单主子表OM_MOMain.MoidOM_MODetails.MODetailsID
OM_MS
委外结算主子表OM_MatSettleVouch.MSIDOM_MatSettleVouchs.MSDetailsID
OM_Materials委外用料表OM_MOMaterials.MOIDOM_MOMaterials.MOMaterialsID
下面是对应的更新语句
以002账套2010年度数据为例
update ufsystem..ua_identity Set iFatherId=(Select Max(ID) As ID From UFDATA_002_2010..RdRecord),
iChildId=(Select Max(AutoID) As AutoID From UFDATA_002_2010..RdRecords)
Where   cAcc_Id='002' And   cVouchType='rd'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(dlID) As ID From UFDATA_002_2010..DISPATCHlist),
iChildId=(Select Max(AUTOID) As AutoID From UFDATA_002_2010..DISPATCHlists)
Where   cAcc_Id='002' And   cVouchType='dispatch'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(SBVID) As ID From UFDATA_002_2010..SaleBillVouch),
iChildId=(Select Max(AutoID) As AutoID From UFDATA_002_2010..SaleBillVouchs)
Where   cAcc_Id='002' And   cVouchType='billvouch'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(poid) As ID From UFDATA_002_2010..po_POMain),
iChildId=(Select Max(id) As AutoID From UFDATA_002_2010..po_podetails)
Where   cAcc_Id='002' And   cVouchType='POMain'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..JustInVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..JustInVouchs)
Where   cAcc_Id='002' And   cVouchType='ju'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..PU_ArrivalVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..PU_ArrivalVouchs)
Where   cAcc_Id='002' And   cVouchType='puarrival'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..PU_ArrivalVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..PU_ArrivalVouchs)
Where   cAcc_Id='002' And   cVouchType='puarrival'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..PU_AppVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..PU_AppVouchs)
Where   cAcc_Id='002' And   cVouchType='puapp'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(pbvid) As ID From UFDATA_002_2010..PurBillVouch),
iChildId=(Select Max(id) As AutoID From UFDATA_002_2010..PurBillVouchs)
Where   cAcc_Id='002' And   cVouchType='purbill'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(psvid) As ID From UFDATA_002_2010..PurSettleVouch),
iChildId=(Select Max(id) As AutoID From UFDATA_002_2010..PurSettleVouchs)
Where   cAcc_Id='002' And   cVouchType='PURSTID'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..SA_QuoMain),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..SA_Quodetails)
Where   cAcc_Id='002' And   cVouchType='QUOMAIN'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..SO_SOMain),
iChildId=(Select Max(isosid) As isosidFrom UFDATA_002_2010..SO_SOdetails)
Where   cAcc_Id='002' And   cVouchType='somain'
update ufsystem..ua_identity Set iFatherId=(Select Max(iid) As ID From UFDATA_002_2010..Ap_CloseBill),
iChildId=(Select Max(id) As AutoID From UFDATA_002_2010..Ap_CloseBills)
Where   cAcc_Id='002' And   cVouchType='sk'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..TransVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..TransVouchs)
Where   cAcc_Id='002' And   cVouchType='tr'
go
update ufsystem..ua_identity Set iFatherId=(Select Max(id) As ID From UFDATA_002_2010..ExpenseVouch),
iChildId=(Select Max(autoid) As AutoID From UFDATA_002_2010..ExpenseVouchS)
Where   cAcc_Id='002' And   cVouchType='EXPENSE'
go

深夜地下铁

想修改哪一账单据的最大单据号 就根据 后面的 语句  稍作修改 执行 就好
这个是我公司一个高手收藏很久，非常好用的语句