※このページは業者様向け情報です。
※サンプルです。
目次
項目データ移動例
※移動先にデータが入っていないことが大前提です。
※バージョンにより場所が違いますので十分にご注意ください。
1 2 3 | UPDATE Believer SET Believer.Bikou1= Believer.Bikou4 FROM Believer; |
例：Bikou4のデータを削除
1 2 3 | UPDATE Believer SET Believer.Bikou4= '' FROM Believer; |
例：屋号に「般00001」のように、任意の文字＋家番号でデータを生成・埋め込みする
1 2 | UPDATE Believer SET Yagou = CONCAT('一般', RIGHT(REPLICATE('0', 5) + CAST(HouseNo AS VARCHAR(5)), 5)) |
名簿に地区名が入っていない場合は地区コードを0にしなければいけません
1 2 3 4 | UPDATE Believer SET Believer.SectionCD= '0' FROM Believer WHERE SectionName = ''; |
逆墓地コードセット
※墓地マスターと名簿の整合性を調べて墓地コードをセット
1 2 3 4 | UPDATE Believer SET Believer.AcreCD = M_Acre.ID FROM Believer INNER JOIN M_Acre ON M_Acre.Name = Believer.AcreName |
例：1番から200番の家に墓地名「沙羅霊園」墓地コード「１」墓地番号に家番号を一括入力
1 2 3 4 5 | UPDATE Believer SET AcreNo = HouseNo, AcreName = '沙羅霊園', AcreCD = 1 WHERE HouseNo >= 1 AND HouseNo <= 200; |
逆地区コードセット
※地区マスターと名簿の整合性を調べて地区コードをセット
1 2 3 4 | UPDATE Believer SET Believer.SectionCD = M_Section.ID FROM Believer INNER JOIN M_Section ON M_Section.Name = Believer.SectionName |
過去帳の地区更新
※名簿の地区名が整合化されている場合に限る
1 2 3 4 | UPDATE Old SET Old.SectionCD = Believer.SectionCD FROM Old INNER JOIN Believer ON Old.HouseNo = Believer.HouseNo; |
命日の1900-01-01をNULLにする
1 2 3 | UPDATE OLD SET DeathDate = REPLACE(DeathDate,'1900-01-01',Null) WHERE DeathDate = '1900-01-01' |
女性セット用
1 2 | UPDATE dbo_Genzai1 SET dbo_Genzai1.Sex = "女" WHERE (((dbo_Genzai1.Name) Like "小百合" Or (dbo_Genzai1.Name) Like "早百合" Or (dbo_Genzai1.Name) Like "沙友里" Or (dbo_Genzai1.Name) Like "梨紗" Or (dbo_Genzai1.Name) Like "理沙" Or (dbo_Genzai1.Name) Like "沙羅" Or (dbo_Genzai1.Name) Like "*奈" Or (dbo_Genzai1.Name) Like "絵里*" Or (dbo_Genzai1.Name) Like "*理" Or (dbo_Genzai1.Name) Like "由香" Or (dbo_Genzai1.Name) Like "由佳" Or (dbo_Genzai1.Name) Like "*み" Or (dbo_Genzai1.Name) Like "*ね" Or (dbo_Genzai1.Name) Like "恵理*" Or (dbo_Genzai1.Name) Like "*ミ" Or (dbo_Genzai1.Name) Like "*ネ" Or (dbo_Genzai1.Name) Like "*い" Or (dbo_Genzai1.Name) Like "千秋" Or (dbo_Genzai1.Name) Like "*カ" Or (dbo_Genzai1.Name) Like "*枝" Or (dbo_Genzai1.Name) Like "*シ" Or (dbo_Genzai1.Name) Like "*乃" Or (dbo_Genzai1.Name) Like "*う" Or (dbo_Genzai1.Name) Like "*セ" Or (dbo_Genzai1.Name) Like "美登里" or (dbo_Genzai1.Name) Like "聖加" or (dbo_Genzai1.Name) Like "*はる" or (dbo_Genzai1.Name) Like "*愛*" or (dbo_Genzai1.Name) Like "千明" or (dbo_Genzai1.Name) Like "*よ" or (dbo_Genzai1.Name) Like "さくら" or (dbo_Genzai1.Name) Like "さち" or (dbo_Genzai1.Name) Like "シゲ" Or (dbo_Genzai1.Name) Like "*の" Or (dbo_Genzai1.Name) Like "*の" Or(dbo_Genzai1.Name) Like "*加奈" Or(dbo_Genzai1.Name) Like "まゆ" Or(dbo_Genzai1.Name) Like "*采" Or(dbo_Genzai1.Name) Like "*桜" Or(dbo_Genzai1.Name) Like "つや" Or(dbo_Genzai1.Name) Like "*を" Or(dbo_Genzai1.Name) Like "*華" Or(dbo_Genzai1.Name) Like "*衣" Or(dbo_Genzai1.Name) Like "*采" Or(dbo_Genzai1.Name) Like "*耶" Or(dbo_Genzai1.Name) Like "*佑衣" Or(dbo_Genzai1.Name) Like "*美奈" Or(dbo_Genzai1.Name) Like "*あや" Or(dbo_Genzai1.Name) Like "*さ" Or(dbo_Genzai1.Name) Like "*優衣" Or(dbo_Genzai1.Name) Like "*ありさ" Or(dbo_Genzai1.Name) Like "美由紀" Or(dbo_Genzai1.Name) Like "*る" Or(dbo_Genzai1.Name) Like "優加" Or(dbo_Genzai1.Name) Like "*な" Or(dbo_Genzai1.Name) Like "すみれ" Or(dbo_Genzai1.Name) Like "*三奈" Or(dbo_Genzai1.Name) Like "*き")); |
還暦検索などの年祝いデータSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | USE [sara2019] GO DELETE from [dbo].[M_YearCelebration] INSERT INTO [dbo].[M_YearCelebration]([ID],[Name],[Age],[Sex],[SortNo],[TargetYear]) VALUES (1,'十三詣',13,'男',1,2013), (2,'成人式',20,'男',3,2006), (4,'緑寿',66,'男',5,1960), (5,'古希',70,'男',7,1956), (6,'喜寿',77,'男',9,1949), (7,'傘寿',80,'男',11,1946), (8,'半寿',81,'男',13,1945), (9,'米寿',88,'男',15,1938), (10,'卒寿',90,'男',17,1936), (11,'白寿',99,'男',19,1927), (12,'上寿',100,'男',21,1926), (13,'茶寿',108,'男',23,1918), (14,'皇寿',111,'男',25,1915), (15,'大還暦',120,'男',27,1906), (17,'七五三(袴着)',5,'男',29,2021), (18,'七五三(帯解)',7,'女',30,2019), (19,'前厄',2,'男',31,2024), (20,'(本厄)七五三(髮置)',3,'男',32,2023), (21,'後厄',4,'男',33,2022), (22,'前厄',23,'男',34,2003), (23,'本厄',24,'男',35,2002), (24,'後厄',25,'男',36,2001), (25,'前厄',40,'男',37,1986), (26,'本厄',41,'男',38,1985), (27,'後厄',42,'男',39,1984), (28,'前厄',60,'男',40,1966), (29,'(本厄)還暦',61,'男',41,1965), (30,'後厄',62,'男',42,1964), (31,'前厄',2,'女',43,2024), (32,'(本厄)七五三(髮置)',3,'女',44,2023), (33,'後厄',4,'女',45,2022), (34,'前厄',17,'女',46,2009), (35,'本厄',18,'女',47,2008), (36,'後厄',19,'女',48,2007), (37,'前厄',31,'女',49,1995), (38,'本厄',32,'女',50,1994), (39,'後厄',33,'女',51,1993), (40,'前厄',35,'女',52,1991), (41,'本厄',36,'女',53,1990), (42,'後厄',37,'女',54,1989), (43,'前厄',60,'女',55,1966), (44,'(本厄)還暦',61,'女',56,1965), (45,'後厄',62,'女',57,1964), (46,'十三詣',13,'女',2,2013), (47,'成人式',20,'女',4,2006), (48,'緑寿',66,'女',6,1960), (49,'古希',70,'女',8,1956), (50,'喜寿',77,'女',10,1949), (51,'傘寿',80,'女',12,1946), (52,'半寿',81,'女',14,1945), (53,'米寿',88,'女',16,1938), (54,'卒寿',90,'女',18,1936), (55,'白寿',99,'女',20,1927), (56,'上寿',100,'女',22,1926), (57,'茶寿',108,'女',24,1918), (58,'皇寿',111,'女',26,1915), (59,'大還暦',120,'女',28,1906); |
宛名
1 2 | UPDATE dbo.Believer SET dbo.Believer .FamilyName2=dbo.dai2.FamilyName2, dbo.Believer .Name2=dbo.dai2.Name2 from dbo.dai2 LEFT OUTER JOIN dbo.Believer ON dbo.dai2.HouseNo = dbo.Believer.HouseNo |
性別変換
コンバートデータにより名称が異なる場合など
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | UPDATE dbo.Believer SET dbo.Believer.sex='M' where dbo.believer.sex = '男' UPDATE dbo.Believer SET dbo.Believer.sex='F' where dbo.believer.sex = '女' UPDATE dbo.Old SET dbo.Old.sex='M' where dbo.Old.sex = '男' UPDATE dbo.Old SET dbo.Old.sex='F' where dbo.Old.sex = '女' UPDATE dbo.Genzai SET dbo.Genzai.sex='M' where dbo.Genzai.sex = '男' UPDATE dbo.Genzai SET dbo.Genzai.sex='F' where dbo.Genzai.sex = '女' UPDATE dbo.Believer SET dbo.Believer.sex='M' where dbo.believer.sex = 'Ma' UPDATE dbo.Believer SET dbo.Believer.sex='F' where dbo.believer.sex = 'Fe' UPDATE dbo.Old SET dbo.Old.sex='M' where dbo.Old.sex = 'Ma' UPDATE dbo.Old SET dbo.Old.sex='F' where dbo.Old.sex = 'Fe' UPDATE dbo.Genzai SET dbo.Genzai.sex='M' where dbo.Genzai.sex = 'Ma' UPDATE dbo.Genzai SET dbo.Genzai.sex='F' where dbo.Genzai.sex = 'Fe' |
