用友修改最大单据标识SQL大全

jojo_bj · 发表于 2011-1-17 09:00:47

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？注册账号

×

本帖最后由 jojo_bj 于 2016-2-29 11:06 编辑

违反了 primary key约束'****'不能在对象'***'中插入重复键

没有工具简单，但是会了这个工具神马都不是
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'
go
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

mianbao5 · 发表于 2013-12-5 22:57:08

不错，好东西，以后肯定有用，先收藏一下

從此丶狠無聊 · 发表于 2017-3-3 09:55:01

好贴解决我问题了非常感谢

peter0503 · 发表于 2016-11-28 15:01:38

好贴，谢谢，解决大问题了

yhs774109 · 发表于 2016-8-11 14:52:06

新手学习，感谢楼主

txb2046 · 发表于 2013-12-8 13:19:59

看不懂，正在学习中。

小_A · 发表于 2011-1-17 09:51:24

sql操盘手！

cexxgu · 发表于 2011-1-17 10:47:36

好东西，值得表扬！！！

lspfjm · 发表于 2011-1-17 11:51:52

这个不是最大单据号，而是主子表的最大ID，最大单据流水号是在表VoucherHistory中记录的
楼主这个更新最大ID的语句还是很强大的

chzxm66 · 发表于 2011-1-18 13:20:59

学习学习谢谢！！

falcon2008 · 发表于 2011-1-18 14:59:41

留着备用呵呵多谢分享哈

psp0815 · 发表于 2011-1-22 14:45:13

好复杂，简直看不懂。。

huang3327872 · 发表于 2011-1-23 11:21:08

看不懂啊。。。

sufei127 · 发表于 2011-1-24 14:40:52

真行，以后要是SQL有问题，就请你指教了。

pengye83 · 发表于 2011-1-25 14:43:17

不知道意义何在？

longyiqiang · 发表于 2011-1-26 14:50:54

不错，sql语句很强

潇洒走一回 · 发表于 2011-3-2 14:14:52

学习一下，我也遇到这个问题

Z.man · 发表于 2011-3-2 14:33:22

en,肯做出这个来分享，也不错

zgf1872 · 发表于 2011-3-3 09:02:58

sql操盘手！

uulluu · 发表于 2011-3-10 15:24:14

很好

账号		自动登录	找回密码
密码			注册账号

初学者课程：T3自学\|T6自学\|U8自学	软件下载｜课件下载｜工具下载	资料：通资料\|U8资料\|NC\|培训\|年结	积分规则 \| 使用常见问题Q&A
知识库:U8 \| 通 \| NC \| U9 \| OA \| 政务	U8\|U9\|NCC\|NC65\|NC65客开\|NCC客开	新手必读 \| 任务 \| 快速增金币	用友QQ群[微信群]

[经验] 用友修改最大单据标识SQL大全

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

相关帖子