2023年8月3日发(作者：)
OracleSQL：update更新语句总结update语句总结update SQL⽤途：⽤于修改表中的数据
语法：UPDATE 表名称 SET 列名称 = 新值
update应⽤实例：
使⽤运算式或数据值更新数据 --更新terry该GP⽤⼾信息 update zx_file
set zx02='诸葛钱好', zxdate = to_date('2012/06/19','YYYY/MM/DD'), --⽇期格式化 zxuser = DEFAULT --⽤DEFAULT提供的默认值 where zx01 = 'terry';使⽤⼦查询更新关联数据--将Tiptop GP系统中所有没有登录过的⽤户密码、开⽴⽇期更新为与terry⽤户的⼀样update zx_file
set (zx10,zxdate) = (select zx10,zxdate from zx_file where zx01 = 'terry') where zx19 = 'N';复制表数据：根据⼀个表的数据更新另外⼀个表的数据--將Tiptop GP系統分群碼imz_file的資料複製更新到料件基本資料ima_file中對應的欄位
update ima_file
set ima_07= (select imz_07 from imz_file where imz_01 = ima_06),--ABC碼 ima_08= (select imz_08 from imz_file where imz_01 = ima_06),--來源碼 ima_09= (select imz_09 from imz_file where imz_01 = ima_06),--其它分群碼⼀ ima_10= (select imz_10 from imz_file where imz_01 = ima_06),--其它分群碼⼆ ima_11= (select imz_11 from imz_file where imz_01 = ima_06),--其它分群碼三 ima_12= (select imz_12 from imz_file where imz_01 = ima_06),--其它分群碼四 ima_14= (select imz_14 from imz_file where imz_01 = ima_06),--是否為⼯程料件 ima_15= (select imz_15 from imz_file where imz_01 = ima_06),--保稅與否 ima_19= (select imz_19 from imz_file where imz_01 = ima_06),--保稅料件進出⼝分類統計⽤類 ima_21= (select imz_21 from imz_file where imz_01 = ima_06),--保稅料件稅則編號 ima_23= (select imz_23 from imz_file where imz_01 = ima_06),--倉管員 ima_24= (select imz_24 from imz_file where imz_01 = ima_06),--檢驗否 ima_25= (select imz_25 from imz_file where imz_01 = ima_06),--庫存單位 ima_27= (select imz_27 from imz_file where imz_01 = ima_06),--安全庫存量 ima_28= (select imz_28 from imz_file where imz_01 = ima_06),--安全庫存期間 ima_31= (select imz_31 from imz_file where imz_01 = ima_06),--銷售單位 ima_31_fac=(select imz_31_fac from imz_file where imz_01 = ima_06),--銷售單位/庫存單位換算率 ima_34= (select imz_34 from imz_file where imz_01 = ima_06),--成本中⼼ ima_35= (select imz_35 from imz_file where imz_01 = ima_06),--主要倉庫別 ima_36= (select imz_36 from imz_file where imz_01 = ima_06),--主要儲位別 ima_37= (select imz_37 from imz_file where imz_01 = ima_06),--補貨策略 ima_38= (select imz_38 from imz_file where imz_01 = ima_06),--再補貨點 ima_39= (select imz_39 from imz_file where imz_01 = ima_06),--料件所屬會計科⽬ ima_42= (select imz_42 from imz_file where imz_01 = ima_06),--批號追蹤⽅式 ima_43= (select imz_43 from imz_file where imz_01 = ima_06),--採購員 ima_44= (select imz_44 from imz_file where imz_01 = ima_06),--採購單位 ima_44_fac=(select imz_44_fac from imz_file where imz_01 = ima_06),--採購單位/庫存單位換算率 ima_45= (select imz_45 from imz_file where imz_01 = ima_06),--採購單位倍量 ima_46= (select imz_46 from imz_file where imz_01 = ima_06),--最少採購量 ima_47 =(select imz_47 from imz_file where imz_01 = ima_06),--採購損耗率 ima_48= (select imz_48 from imz_file where imz_01 = ima_06),--採購安全期 ima_49= (select imz_49 from imz_file where imz_01 = ima_06),--到廠前置期 ima_491=(select imz_491 from imz_file where imz_01 = ima_06),--⼊庫前置期 ima_50= (select imz_50 from imz_file where imz_01 = ima_06),--請購安全期 ima_51= (select imz_51 from imz_file where imz_01 = ima_06),--經濟訂購量 ima_52= (select imz_52 from imz_file where imz_01 = ima_06),--平均訂購量 ima_54= (select imz_54 from imz_file where imz_01 = ima_06),--主要供應商 ima_55= (select imz_55 from imz_file where imz_01 = ima_06),--⽣產單位 ima_55_fac=(select imz_55_fac from imz_file where imz_01 = ima_06),--⽣產單位/庫存單位換算率 ima_56= (select imz_56 from imz_file where imz_01 = ima_06),--⽣產單位倍量 ima_561=(select imz_561 from imz_file where imz_01 = ima_06),--最少⽣產量 ima_562=(select imz_562 from imz_file where imz_01 = ima_06),--⽣產損耗率 ima_571=(select imz_571 from imz_file where imz_01 = ima_06),--主製程料號 ima_59= (select imz_59 from imz_file where imz_01 = ima_06),--固定前置時間 ima_60= (select imz_60 from imz_file where imz_01 = ima_06),--變動前置時間 ima_61= (select imz_61 from imz_file where imz_01 = ima_06),--QC前置時間 ima_62= (select imz_62 from imz_file where imz_01 = ima_06),--最⼤累計前置時間 ima_63= (select imz_63 from imz_file where imz_01 = ima_06),--發料單位 ima_63_fac=(select imz_63_fac from imz_file where imz_01 = ima_06),--發料單位/庫存單位換算率 ima_64= (select imz_64 from imz_file where imz_01 = ima_06),--發料單位倍量 ima_641=(select imz_641 from imz_file where imz_01 = ima_06),--最少發料量 ima_65= (select imz_65 from imz_file where imz_01 = ima_06),--發料安全存量 ima_66= (select imz_66 from imz_file where imz_01 = ima_06),--發料安全期 ima_67= (select imz_67 from imz_file where imz_01 = ima_06),--計畫員 ima_68= (select imz_68 from imz_file where imz_01 = ima_06),--需求時距 ima_69= (select imz_69 from imz_file where imz_01 = ima_06),--計畫時距 ima_70= (select imz_70 from imz_file where imz_01 = ima_06),--消耗料件 ima_71= (select imz_71 from imz_file where imz_01 = ima_06),--儲存有效天數 ima_86= (select imz_86 from imz_file where imz_01 = ima_06),--成本單位 ima_86_fac=(select imz_86_fac from imz_file where imz_01 = ima_06),--成本/庫存單位換算率 ima_87= (select imz_87 from imz_file where imz_01 = ima_06),--成本項⽬ ima_871=(select imz_871 from imz_file where imz_01 = ima_06),--材料製造費⽤分攤率 ima_872=(select imz_872 from imz_file where imz_01 = ima_06),--材料製造費⽤成本項⽬ ima_873=(select imz_873 from imz_file where imz_01 = ima_06),--間接⼈⼯分攤率 ima_874=(select imz_874 from imz_file where imz_01 = ima_06),--⼈⼯製造費⽤成本項⽬ ima_88= (select imz_88 from imz_file where imz_01 = ima_06),--期間採購數量 ima_89= (select imz_89 from imz_file where imz_01 = ima_06),--期間採購使⽤的期間 ima_90= (select imz_90 from imz_file where imz_01 = ima_06),--期間採購使⽤的期間 ima_94= (select imz_94 from imz_file where imz_01 = ima_06),--預設製程編號 (⼯單開⽴時預設之) ima_99= (select imz_99 from imz_file where imz_01 = ima_06),--再補貨量 ima_100=(select imz_100 from imz_file where imz_01 = ima_06),--檢驗程度 ima_101=(select imz_101 from imz_file where imz_01 = ima_06),--檢驗⽔準 ima_102=(select imz_102 from imz_file where imz_01 = ima_06),--級數 ima_103=(select imz_103 from imz_file where imz_01 = ima_06),--採購特性 ima_105=(select imz_105 from imz_file where imz_01 = ima_06),--是否為軟體物件 ima_106=(select imz_106 from imz_file where imz_01 = ima_06),--保稅料件型態 ima_107=(select imz_107 from imz_file where imz_01 = ima_06),--插件位置 ima_108=(select imz_108 from imz_file where imz_01 = ima_06),--⼯單發料前調撥否 ima_109=(select imz_109 from imz_file where imz_01 = ima_06),--材料類別 ima_109=(select imz_109 from imz_file where imz_01 = ima_06),--材料類別 ima_110=(select imz_110 from imz_file where imz_01 = ima_06),--⼯單開⽴展開選項 ima_130=(select imz_130 from imz_file where imz_01 = ima_06),--產品銷售特性 ima_131=(select imz_131 from imz_file where imz_01 = ima_06),--產品分類編號 ima_132=(select imz_132 from imz_file where imz_01 = ima_06),--費⽤科⽬編號 ima_133=(select imz_133 from imz_file where imz_01 = ima_06),--產品預測料號 ima_134=(select imz_134 from imz_file where imz_01 = ima_06),--主要包裝⽅式編號 ima_147=(select imz_147 from imz_file where imz_01 = ima_06),--插件位置與QPA是否要勾稽 ima_148=(select imz_148 from imz_file where imz_01 = ima_06),--保證期(天) ima_903=(select imz_903 from imz_file where imz_01 = ima_06),--可否做聯產品⼊庫 ima_906=(select imz_906 from imz_file where imz_01 = ima_06),--單位使⽤⽅式1.單⼀單位2.母⼦ ima_907=(select imz_907 from imz_file where imz_01 = ima_06),--第⼆單位(母單位/參考單位) ima_908=(select imz_908 from imz_file where imz_01 = ima_06),--計價單位 ima_909=(select imz_909 from imz_file where imz_01 = ima_06),--MRP匯總時距(天)(預留欄位) ima_911=(select imz_911 from imz_file where imz_01 = ima_06),--是否為重覆性⽣產料件 (Y/N) ima_136=(select imz_136 from imz_file where imz_01 = ima_06),--主要WIP 倉庫 ima_137=(select imz_137 from imz_file where imz_01 = ima_06) --主要WIP 儲位
2023年8月3日发(作者：)
OracleSQL：update更新语句总结update语句总结update SQL⽤途：⽤于修改表中的数据
语法：UPDATE 表名称 SET 列名称 = 新值
update应⽤实例：
使⽤运算式或数据值更新数据 --更新terry该GP⽤⼾信息 update zx_file
set zx02='诸葛钱好', zxdate = to_date('2012/06/19','YYYY/MM/DD'), --⽇期格式化 zxuser = DEFAULT --⽤DEFAULT提供的默认值 where zx01 = 'terry';使⽤⼦查询更新关联数据--将Tiptop GP系统中所有没有登录过的⽤户密码、开⽴⽇期更新为与terry⽤户的⼀样update zx_file
set (zx10,zxdate) = (select zx10,zxdate from zx_file where zx01 = 'terry') where zx19 = 'N';复制表数据：根据⼀个表的数据更新另外⼀个表的数据--將Tiptop GP系統分群碼imz_file的資料複製更新到料件基本資料ima_file中對應的欄位
update ima_file
set ima_07= (select imz_07 from imz_file where imz_01 = ima_06),--ABC碼 ima_08= (select imz_08 from imz_file where imz_01 = ima_06),--來源碼 ima_09= (select imz_09 from imz_file where imz_01 = ima_06),--其它分群碼⼀ ima_10= (select imz_10 from imz_file where imz_01 = ima_06),--其它分群碼⼆ ima_11= (select imz_11 from imz_file where imz_01 = ima_06),--其它分群碼三 ima_12= (select imz_12 from imz_file where imz_01 = ima_06),--其它分群碼四 ima_14= (select imz_14 from imz_file where imz_01 = ima_06),--是否為⼯程料件 ima_15= (select imz_15 from imz_file where imz_01 = ima_06),--保稅與否 ima_19= (select imz_19 from imz_file where imz_01 = ima_06),--保稅料件進出⼝分類統計⽤類 ima_21= (select imz_21 from imz_file where imz_01 = ima_06),--保稅料件稅則編號 ima_23= (select imz_23 from imz_file where imz_01 = ima_06),--倉管員 ima_24= (select imz_24 from imz_file where imz_01 = ima_06),--檢驗否 ima_25= (select imz_25 from imz_file where imz_01 = ima_06),--庫存單位 ima_27= (select imz_27 from imz_file where imz_01 = ima_06),--安全庫存量 ima_28= (select imz_28 from imz_file where imz_01 = ima_06),--安全庫存期間 ima_31= (select imz_31 from imz_file where imz_01 = ima_06),--銷售單位 ima_31_fac=(select imz_31_fac from imz_file where imz_01 = ima_06),--銷售單位/庫存單位換算率 ima_34= (select imz_34 from imz_file where imz_01 = ima_06),--成本中⼼ ima_35= (select imz_35 from imz_file where imz_01 = ima_06),--主要倉庫別 ima_36= (select imz_36 from imz_file where imz_01 = ima_06),--主要儲位別 ima_37= (select imz_37 from imz_file where imz_01 = ima_06),--補貨策略 ima_38= (select imz_38 from imz_file where imz_01 = ima_06),--再補貨點 ima_39= (select imz_39 from imz_file where imz_01 = ima_06),--料件所屬會計科⽬ ima_42= (select imz_42 from imz_file where imz_01 = ima_06),--批號追蹤⽅式 ima_43= (select imz_43 from imz_file where imz_01 = ima_06),--採購員 ima_44= (select imz_44 from imz_file where imz_01 = ima_06),--採購單位 ima_44_fac=(select imz_44_fac from imz_file where imz_01 = ima_06),--採購單位/庫存單位換算率 ima_45= (select imz_45 from imz_file where imz_01 = ima_06),--採購單位倍量 ima_46= (select imz_46 from imz_file where imz_01 = ima_06),--最少採購量 ima_47 =(select imz_47 from imz_file where imz_01 = ima_06),--採購損耗率 ima_48= (select imz_48 from imz_file where imz_01 = ima_06),--採購安全期 ima_49= (select imz_49 from imz_file where imz_01 = ima_06),--到廠前置期 ima_491=(select imz_491 from imz_file where imz_01 = ima_06),--⼊庫前置期 ima_50= (select imz_50 from imz_file where imz_01 = ima_06),--請購安全期 ima_51= (select imz_51 from imz_file where imz_01 = ima_06),--經濟訂購量 ima_52= (select imz_52 from imz_file where imz_01 = ima_06),--平均訂購量 ima_54= (select imz_54 from imz_file where imz_01 = ima_06),--主要供應商 ima_55= (select imz_55 from imz_file where imz_01 = ima_06),--⽣產單位 ima_55_fac=(select imz_55_fac from imz_file where imz_01 = ima_06),--⽣產單位/庫存單位換算率 ima_56= (select imz_56 from imz_file where imz_01 = ima_06),--⽣產單位倍量 ima_561=(select imz_561 from imz_file where imz_01 = ima_06),--最少⽣產量 ima_562=(select imz_562 from imz_file where imz_01 = ima_06),--⽣產損耗率 ima_571=(select imz_571 from imz_file where imz_01 = ima_06),--主製程料號 ima_59= (select imz_59 from imz_file where imz_01 = ima_06),--固定前置時間 ima_60= (select imz_60 from imz_file where imz_01 = ima_06),--變動前置時間 ima_61= (select imz_61 from imz_file where imz_01 = ima_06),--QC前置時間 ima_62= (select imz_62 from imz_file where imz_01 = ima_06),--最⼤累計前置時間 ima_63= (select imz_63 from imz_file where imz_01 = ima_06),--發料單位 ima_63_fac=(select imz_63_fac from imz_file where imz_01 = ima_06),--發料單位/庫存單位換算率 ima_64= (select imz_64 from imz_file where imz_01 = ima_06),--發料單位倍量 ima_641=(select imz_641 from imz_file where imz_01 = ima_06),--最少發料量 ima_65= (select imz_65 from imz_file where imz_01 = ima_06),--發料安全存量 ima_66= (select imz_66 from imz_file where imz_01 = ima_06),--發料安全期 ima_67= (select imz_67 from imz_file where imz_01 = ima_06),--計畫員 ima_68= (select imz_68 from imz_file where imz_01 = ima_06),--需求時距 ima_69= (select imz_69 from imz_file where imz_01 = ima_06),--計畫時距 ima_70= (select imz_70 from imz_file where imz_01 = ima_06),--消耗料件 ima_71= (select imz_71 from imz_file where imz_01 = ima_06),--儲存有效天數 ima_86= (select imz_86 from imz_file where imz_01 = ima_06),--成本單位 ima_86_fac=(select imz_86_fac from imz_file where imz_01 = ima_06),--成本/庫存單位換算率 ima_87= (select imz_87 from imz_file where imz_01 = ima_06),--成本項⽬ ima_871=(select imz_871 from imz_file where imz_01 = ima_06),--材料製造費⽤分攤率 ima_872=(select imz_872 from imz_file where imz_01 = ima_06),--材料製造費⽤成本項⽬ ima_873=(select imz_873 from imz_file where imz_01 = ima_06),--間接⼈⼯分攤率 ima_874=(select imz_874 from imz_file where imz_01 = ima_06),--⼈⼯製造費⽤成本項⽬ ima_88= (select imz_88 from imz_file where imz_01 = ima_06),--期間採購數量 ima_89= (select imz_89 from imz_file where imz_01 = ima_06),--期間採購使⽤的期間 ima_90= (select imz_90 from imz_file where imz_01 = ima_06),--期間採購使⽤的期間 ima_94= (select imz_94 from imz_file where imz_01 = ima_06),--預設製程編號 (⼯單開⽴時預設之) ima_99= (select imz_99 from imz_file where imz_01 = ima_06),--再補貨量 ima_100=(select imz_100 from imz_file where imz_01 = ima_06),--檢驗程度 ima_101=(select imz_101 from imz_file where imz_01 = ima_06),--檢驗⽔準 ima_102=(select imz_102 from imz_file where imz_01 = ima_06),--級數 ima_103=(select imz_103 from imz_file where imz_01 = ima_06),--採購特性 ima_105=(select imz_105 from imz_file where imz_01 = ima_06),--是否為軟體物件 ima_106=(select imz_106 from imz_file where imz_01 = ima_06),--保稅料件型態 ima_107=(select imz_107 from imz_file where imz_01 = ima_06),--插件位置 ima_108=(select imz_108 from imz_file where imz_01 = ima_06),--⼯單發料前調撥否 ima_109=(select imz_109 from imz_file where imz_01 = ima_06),--材料類別 ima_109=(select imz_109 from imz_file where imz_01 = ima_06),--材料類別 ima_110=(select imz_110 from imz_file where imz_01 = ima_06),--⼯單開⽴展開選項 ima_130=(select imz_130 from imz_file where imz_01 = ima_06),--產品銷售特性 ima_131=(select imz_131 from imz_file where imz_01 = ima_06),--產品分類編號 ima_132=(select imz_132 from imz_file where imz_01 = ima_06),--費⽤科⽬編號 ima_133=(select imz_133 from imz_file where imz_01 = ima_06),--產品預測料號 ima_134=(select imz_134 from imz_file where imz_01 = ima_06),--主要包裝⽅式編號 ima_147=(select imz_147 from imz_file where imz_01 = ima_06),--插件位置與QPA是否要勾稽 ima_148=(select imz_148 from imz_file where imz_01 = ima_06),--保證期(天) ima_903=(select imz_903 from imz_file where imz_01 = ima_06),--可否做聯產品⼊庫 ima_906=(select imz_906 from imz_file where imz_01 = ima_06),--單位使⽤⽅式1.單⼀單位2.母⼦ ima_907=(select imz_907 from imz_file where imz_01 = ima_06),--第⼆單位(母單位/參考單位) ima_908=(select imz_908 from imz_file where imz_01 = ima_06),--計價單位 ima_909=(select imz_909 from imz_file where imz_01 = ima_06),--MRP匯總時距(天)(預留欄位) ima_911=(select imz_911 from imz_file where imz_01 = ima_06),--是否為重覆性⽣產料件 (Y/N) ima_136=(select imz_136 from imz_file where imz_01 = ima_06),--主要WIP 倉庫 ima_137=(select imz_137 from imz_file where imz_01 = ima_06) --主要WIP 儲位
发布评论