贫困农户信息管理系统常用命令
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
批量删除2010年数据
SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM RegisterTable WHERE TID%100 = 10; DELETE FROM FamilyMember WHERE TID%100 = 10; DELETE FROM RequirementProject WHERE TID%100 = 10; DELETE FROM SupportProject WHERE TID%100 = 10;
IF @@error!=0
ROLLBACK TRANSACTION
ELSE COMMIT TRANSACTION
批量删除2012年数据
SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM RegisterTable WHERE TID%100 = 12; DELETE FROM FamilyMember WHERE TID%100 = 12; DELETE FROM RequirementProject WHERE TID%100 = 12; DELETE FROM SupportProject WHERE TID%100 = 12;
IF @@error!=0
ROLLBACK TRANSACTION
ELSE COMMIT TRANSACTION
删除年份重复
DELETE FROM FamilyMember
WHERE TID%100 = 11 AND MemberID IN
(
SELECT MemberID FROM FamilyMember A
WHERE EXISTS
(SELECT * FROM FamilyMember B WHERE B.MemberID!=A.MemberID AND
substring(A.MemberID, 1, 16) = substring(B.MemberID, 1, 16) AND substring(A.MemberID, 19, 4) = substring(B.MemberID, 19, 4))
)
DELETE FROM RequirementProject
WHERE TID%100 = 11 AND ProjectID IN
(
SELECT ProjectID FROM RequirementProject A
WHERE EXISTS
(SELECT * FROM RequirementProject B WHERE B.ProjectID!=A.ProjectID AND
substring(A.ProjectID, 1, 16) = substring(B.ProjectID, 1, 16) AND substring(A.ProjectID, 19, 4) = substring(B.ProjectID, 19, 4))
)
编码重复
SELECT * FROM RegisterTable A
WHERE TID%100 !=10 AND EXISTS(SELECT * FROM RegisterTable B WHERE B.FarmerID=A.FarmerID AND B.TID != A.TID)
省扶贫标准批量修改成国家标准：
UPDA TE RegisterTable SET SupportLevel= 1 WHERE SupportLevel=2
去掉不同年份建档重复户
-- 注意:执行前做好数据备份
SET XACT_ABORT ON
BEGIN TRANSACTION
UPDA TE RegisterTable SET ExtendCharField7 = 'chongfu' WHERE TID IN
(
SELECT TID FROM vuRegisterTable A
WHERE EXISTS(SELECT TID FROM vuRegisterTable B
WHERE B.TID ) ); DELETE FROM FamilyMember WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = 'chongfu'); DELETE FROM RequirementProject WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = 'chongfu'); DELETE FROM SupportProject WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = 'chongfu'); DELETE FROM RegisterTable WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = 'chongfu'); IF @@error!=0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 1、批量修改养老保险命令{是变为否}： UPDA TE RegisterTable SET Y anglaoBaoxian = 2 WHERE Y anglaoBaoxian =1