※このページはサポート業者様向け情報です。
※サンプルです。実際に使う場合はご連絡ください。
年号が入ったAccess
1 2 3 4 5 | UPDATE dbo.Old SET dbo.Old.DeathDate = [kako-date].Dathdate,dbo.Old.JapanYear = [kako-date].JapanYear FROM dbo.Old INNER JOIN dbo.[kako-date] ON dbo.Old.ID = [kako-date].ID; |
項目データ移動例
※移動先にデータが入っていないことが大前提です。
※バージョンにより場所が違いますので十分にご注意ください。
1 2 3 | UPDATE Believer SET Believer.Bikou1= Believer.Bikou4 FROM Believer; |
例：役職１をBikou5へ
1 2 3 | UPDATE Believer SET Believer.Bikou5= Believer.Yakushoku1 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 = ''; |
kako-dateのDeathDateをOLDに」
1 2 3 4 5 | UPDATE dbo.Old SET dbo.Old.DeathDate = [kako-date].Dathdate FROM dbo.Old INNER JOIN dbo.[kako-date] ON dbo.Old.ID = [kako-date].ID; |
逆墓地コードセット
※墓地マスターと名簿の整合性を調べて墓地コードをセット
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' |
家番号0の過去帳の世帯主を空にする
1 2 3 4 | UPDATE Old SET Old.HouseHoldname= '' FROM Old WHERE HouseNo = '0'; |
該当データを書き換え
例：HouseNo,Yagou.Bikou1 の別テーブルデータを送り、
Believerの該当データを更新する
1 2 3 4 5 | UPDATE b SET b.Yagou = t.yagou FROM Believer AS b INNER JOIN Tuika AS t ON b.HouseNo = t.HouseNo; |
家族テーブルに誕生日（yyyy/MM/dd）の形式で入っているデータの整形
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 | UPDATE Genzai SET BirthNen = YEAR(BirthDay), BirthTuki = MONTH(BirthDay), BirthHi = DAY(BirthDay), BirthWareki = CASE WHEN BirthDay >= '2019-05-01' THEN '令和' WHEN BirthDay >= '1989-01-08' THEN '平成' WHEN BirthDay >= '1926-12-25' THEN '昭和' WHEN BirthDay >= '1912-07-30' THEN '大正' WHEN BirthDay >= '1868-01-25' THEN '明治' ELSE '' -- ★ 明治以前は絶対に変換しない END, BirthJapanYear = CASE WHEN BirthDay >= '2019-05-01' THEN YEAR(BirthDay) - 2018 WHEN BirthDay >= '1989-01-08' THEN YEAR(BirthDay) - 1988 WHEN BirthDay >= '1926-12-25' THEN YEAR(BirthDay) - 1925 WHEN BirthDay >= '1912-07-30' THEN YEAR(BirthDay) - 1911 WHEN BirthDay >= '1868-01-25' THEN YEAR(BirthDay) - 1867 ELSE 0 -- ★ 明治以前は和暦年を設定しない END, Eto = CASE ((YEAR(BirthDay) + 8) % 12) WHEN 0 THEN '子' WHEN 1 THEN '丑' WHEN 2 THEN '寅' WHEN 3 THEN '卯' WHEN 4 THEN '辰' WHEN 5 THEN '巳' WHEN 6 THEN '午' WHEN 7 THEN '未' WHEN 8 THEN '申' WHEN 9 THEN '酉' WHEN 10 THEN '戌' WHEN 11 THEN '亥' END WHERE BirthDay IS NOT NULL AND BirthDay >= '1868-01-25' -- ★ 明治以前は変換対象外 AND BirthDay <= GETDATE(); -- 未来日も排除 |
