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_file.ima07= (select imz_file.imz07 from imz_file where imz_file.imz01 = ima_file.ima06),--ABC碼
ima_file.ima08= (select imz_file.imz08 from imz_file where imz_file.imz01 = ima_file.ima06),--來源碼
ima_file.ima09= (select imz_file.imz09 from imz_file where imz_file.imz01 = ima_file.ima06),--其它分群碼一
ima_file.ima10= (select imz_file.imz10 from imz_file where imz_file.imz01 = ima_file.ima06),--其它分群碼二
ima_file.ima11= (select imz_file.imz11 from imz_file where imz_file.imz01 = ima_file.ima06),--其它分群碼三
ima_file.ima12= (select imz_file.imz12 from imz_file where imz_file.imz01 = ima_file.ima06),--其它分群碼四
ima_file.ima14= (select imz_file.imz14 from imz_file where imz_file.imz01 = ima_file.ima06),--是否為工程料件
ima_file.ima15= (select imz_file.imz15 from imz_file where imz_file.imz01 = ima_file.ima06),--保稅與否
ima_file.ima19= (select imz_file.imz19 from imz_file where imz_file.imz01 = ima_file.ima06),--保稅料件進出口分類統計用類
ima_file.ima21= (select imz_file.imz21 from imz_file where imz_file.imz01 = ima_file.ima06),--保稅料件稅則編號
ima_file.ima23= (select imz_file.imz23 from imz_file where imz_file.imz01 = ima_file.ima06),--倉管員
ima_file.ima24= (select imz_file.imz24 from imz_file where imz_file.imz01 = ima_file.ima06),--檢驗否
ima_file.ima25= (select imz_file.imz25 from imz_file where imz_file.imz01 = ima_file.ima06),--庫存單位
ima_file.ima27= (select imz_file.imz27 from imz_file where imz_file.imz01 = ima_file.ima06),--安全庫存量
ima_file.ima28= (select imz_file.imz28 from imz_file where imz_file.imz01 = ima_file.ima06),--安全庫存期間
ima_file.ima31= (select imz_file.imz31 from imz_file where imz_file.imz01 = ima_file.ima06),--銷售單位
ima_file.ima31_fac=(select imz_file.imz31_fac from imz_file where imz_file.imz01 = ima_file.ima06),--銷售單位/庫存單位換算率
ima_file.ima34= (select imz_file.imz34 from imz_file where imz_file.imz01 = ima_file.ima06),--成本中心
ima_file.ima35= (select imz_file.imz35 from imz_file where imz_file.imz01 = ima_file.ima06),--主要倉庫別
ima_file.ima36= (select imz_file.imz36 from imz_file where imz_file.imz01 = ima_file.ima06),--主要儲位別
ima_file.ima37= (select imz_file.imz37 from imz_file where imz_file.imz01 = ima_file.ima06),--補貨策略
ima_file.ima38= (select imz_file.imz38 from imz_file where imz_file.imz01 = ima_file.ima06),--再補貨點
ima_file.ima39= (select imz_file.imz39 from imz_file where imz_file.imz01 = ima_file.ima06),--料件所屬會計科目
ima_file.ima42= (select imz_file.imz42 from imz_file where imz_file.imz01 = ima_file.ima06),--批號追蹤方式
ima_file.ima43= (select imz_file.imz43 from imz_file where imz_file.imz01 = ima_file.ima06),--採購員
ima_file.ima44= (select imz_file.imz44 from imz_file where imz_file.imz01 = ima_file.ima06),--採購單位
ima_file.ima44_fac=(select imz_file.imz44_fac from imz_file where imz_file.imz01 = ima_file.ima06),--採購單位/庫存單位換算率
ima_file.ima45= (select imz_file.imz45 from imz_file where imz_file.imz01 = ima_file.ima06),--採購單位倍量
ima_file.ima46= (select imz_file.imz46 from imz_file where imz_file.imz01 = ima_file.ima06),--最少採購量
ima_file.ima47 =(select imz_file.imz47 from imz_file where imz_file.imz01 = ima_file.ima06),--採購損耗率
ima_file.ima48= (select imz_file.imz48 from imz_file where imz_file.imz01 = ima_file.ima06),--採購安全期
ima_file.ima49= (select imz_file.imz49 from imz_file where imz_file.imz01 = ima_file.ima06),--到廠前置期
ima_file.ima491=(select imz_file.imz491 from imz_file where imz_file.imz01 = ima_file.ima06),--入庫前置期
ima_file.ima50= (select imz_file.imz50 from imz_file where imz_file.imz01 = ima_file.ima06),--請購安全期
ima_file.ima51= (select imz_file.imz51 from imz_file where imz_file.imz01 = ima_file.ima06),--經濟訂購量
ima_file.ima52= (select imz_file.imz52 from imz_file where imz_file.imz01 = ima_file.ima06),--平均訂購量
ima_file.ima54= (select imz_file.imz54 from imz_file where imz_file.imz01 = ima_file.ima06),--主要供應商
ima_file.ima55= (select imz_file.imz55 from imz_file where imz_file.imz01 = ima_file.ima06),--生產單位
ima_file.ima55_fac=(select imz_file.imz55_fac from imz_file where imz_file.imz01 = ima_file.ima06),--生產單位/庫存單位換算率
ima_file.ima56= (select imz_file.imz56 from imz_file where imz_file.imz01 = ima_file.ima06),--生產單位倍量
ima_file.ima561=(select imz_file.imz561 from imz_file where imz_file.imz01 = ima_file.ima06),--最少生產量
ima_file.ima562=(select imz_file.imz562 from imz_file where imz_file.imz01 = ima_file.ima06),--生產損耗率
ima_file.ima571=(select imz_file.imz571 from imz_file where imz_file.imz01 = ima_file.ima06),--主製程料號
ima_file.ima59= (select imz_file.imz59 from imz_file where imz_file.imz01 = ima_file.ima06),--固定前置時間
ima_file.ima60= (select imz_file.imz60 from imz_file where imz_file.imz01 = ima_file.ima06),--變動前置時間
ima_file.ima61= (select imz_file.imz61 from imz_file where imz_file.imz01 = ima_file.ima06),--QC前置時間
ima_file.ima62= (select imz_file.imz62 from imz_file where imz_file.imz01 = ima_file.ima06),--最大累計前置時間
ima_file.ima63= (select imz_file.imz63 from imz_file where imz_file.imz01 = ima_file.ima06),--發料單位
ima_file.ima63_fac=(select imz_file.imz63_fac from imz_file where imz_file.imz01 = ima_file.ima06),--發料單位/庫存單位換算率
ima_file.ima64= (select imz_file.imz64 from imz_file where imz_file.imz01 = ima_file.ima06),--發料單位倍量
ima_file.ima641=(select imz_file.imz641 from imz_file where imz_file.imz01 = ima_file.ima06),--最少發料量
ima_file.ima65= (select imz_file.imz65 from imz_file where imz_file.imz01 = ima_file.ima06),--發料安全存量
ima_file.ima66= (select imz_file.imz66 from imz_file where imz_file.imz01 = ima_file.ima06),--發料安全期
ima_file.ima67= (select imz_file.imz67 from imz_file where imz_file.imz01 = ima_file.ima06),--計畫員
ima_file.ima68= (select imz_file.imz68 from imz_file where imz_file.imz01 = ima_file.ima06),--需求時距
ima_file.ima69= (select imz_file.imz69 from imz_file where imz_file.imz01 = ima_file.ima06),--計畫時距
ima_file.ima70= (select imz_file.imz70 from imz_file where imz_file.imz01 = ima_file.ima06),--消耗料件
ima_file.ima71= (select imz_file.imz71 from imz_file where imz_file.imz01 = ima_file.ima06),--儲存有效天數
ima_file.ima86= (select imz_file.imz86 from imz_file where imz_file.imz01 = ima_file.ima06),--成本單位
ima_file.ima86_fac=(select imz_file.imz86_fac from imz_file where imz_file.imz01 = ima_file.ima06),--成本/庫存單位換算率
ima_file.ima87= (select imz_file.imz87 from imz_file where imz_file.imz01 = ima_file.ima06),--成本項目
ima_file.ima871=(select imz_file.imz871 from imz_file where imz_file.imz01 = ima_file.ima06),--材料製造費用分攤率
ima_file.ima872=(select imz_file.imz872 from imz_file where imz_file.imz01 = ima_file.ima06),--材料製造費用成本項目
ima_file.ima873=(select imz_file.imz873 from imz_file where imz_file.imz01 = ima_file.ima06),--間接人工分攤率
ima_file.ima874=(select imz_file.imz874 from imz_file where imz_file.imz01 = ima_file.ima06),--人工製造費用成本項目
ima_file.ima88= (select imz_file.imz88 from imz_file where imz_file.imz01 = ima_file.ima06),--期間採購數量
ima_file.ima89= (select imz_file.imz89 from imz_file where imz_file.imz01 = ima_file.ima06),--期間採購使用的期間
ima_file.ima90= (select imz_file.imz90 from imz_file where imz_file.imz01 = ima_file.ima06),--期間採購使用的期間
ima_file.ima94= (select imz_file.imz94 from imz_file where imz_file.imz01 = ima_file.ima06),--預設製程編號 (工單開立時預設之)
ima_file.ima99= (select imz_file.imz99 from imz_file where imz_file.imz01 = ima_file.ima06),--再補貨量
ima_file.ima100=(select imz_file.imz100 from imz_file where imz_file.imz01 = ima_file.ima06),--檢驗程度
ima_file.ima101=(select imz_file.imz101 from imz_file where imz_file.imz01 = ima_file.ima06),--檢驗水準
ima_file.ima102=(select imz_file.imz102 from imz_file where imz_file.imz01 = ima_file.ima06),--級數
ima_file.ima103=(select imz_file.imz103 from imz_file where imz_file.imz01 = ima_file.ima06),--採購特性
ima_file.ima105=(select imz_file.imz105 from imz_file where imz_file.imz01 = ima_file.ima06),--是否為軟體物件
ima_file.ima106=(select imz_file.imz106 from imz_file where imz_file.imz01 = ima_file.ima06),--保稅料件型態
ima_file.ima107=(select imz_file.imz107 from imz_file where imz_file.imz01 = ima_file.ima06),--插件位置
ima_file.ima108=(select imz_file.imz108 from imz_file where imz_file.imz01 = ima_file.ima06),--工單發料前調撥否
ima_file.ima109=(select imz_file.imz109 from imz_file where imz_file.imz01 = ima_file.ima06),--材料類別
ima_file.ima110=(select imz_file.imz110 from imz_file where imz_file.imz01 = ima_file.ima06),--工單開立展開選項
ima_file.ima130=(select imz_file.imz130 from imz_file where imz_file.imz01 = ima_file.ima06),--產品銷售特性
ima_file.ima131=(select imz_file.imz131 from imz_file where imz_file.imz01 = ima_file.ima06),--產品分類編號
ima_file.ima132=(select imz_file.imz132 from imz_file where imz_file.imz01 = ima_file.ima06),--費用科目編號
ima_file.ima133=(select imz_file.imz133 from imz_file where imz_file.imz01 = ima_file.ima06),--產品預測料號
ima_file.ima134=(select imz_file.imz134 from imz_file where imz_file.imz01 = ima_file.ima06),--主要包裝方式編號
ima_file.ima147=(select imz_file.imz147 from imz_file where imz_file.imz01 = ima_file.ima06),--插件位置與QPA是否要勾稽
ima_file.ima148=(select imz_file.imz148 from imz_file where imz_file.imz01 = ima_file.ima06),--保證期(天)
ima_file.ima903=(select imz_file.imz903 from imz_file where imz_file.imz01 = ima_file.ima06),--可否做聯產品入庫
ima_file.ima906=(select imz_file.imz906 from imz_file where imz_file.imz01 = ima_file.ima06),--單位使用方式1.單一單位2.母子
ima_file.ima907=(select imz_file.imz907 from imz_file where imz_file.imz01 = ima_file.ima06),--第二單位(母單位/參考單位)
ima_file.ima908=(select imz_file.imz908 from imz_file where imz_file.imz01 = ima_file.ima06),--計價單位
ima_file.ima909=(select imz_file.imz909 from imz_file where imz_file.imz01 = ima_file.ima06),--MRP匯總時距(天)(預留欄位)
ima_file.ima911=(select imz_file.imz911 from imz_file where imz_file.imz01 = ima_file.ima06),--是否為重覆性生產料件 (Y/N)
ima_file.ima136=(select imz_file.imz136 from imz_file where imz_file.imz01 = ima_file.ima06),--主要WIP 倉庫
ima_file.ima137=(select imz_file.imz137 from imz_file where imz_file.imz01 = ima_file.ima06) --主要WIP 儲位
http://blog.csdn.net/yihuiworld
我们在使用
UPDATE
更新
语句
更改表中数据时，可能会导致表中产生行级锁或者是表级锁。
UPDATE
语句
的优化就是为了避免表中出现表级锁，从而影响并发的性能。当
UPDATE
语句
更新
表数据时，WHERE条件使用的是索引字段，那么此时会出现行级锁，只是锁住这一行数据，对表中其他的数据没有任何影响，性能最高，但是当WHERE条件使用的不是索引字段时，此时就会出现表级锁，只有当
UPDATE
语句
的事务提交完毕，表级锁才会释放，大大影响并发的性能。1）
UPDATE
语句
WHERE条件使用索引字段作为检索条件当
UPDATE
语句
1.联表
更新
UPDATE
T_DEV_ASSET_ACCOUNT
SET T_DEV_ASSET_ACCOUNT.DEVICE_TYPE = ( SE
LE
CT ID FROM T_DEV_DEVICE_TYPE WHERE T_DEV_ASSET_ACCOUNT.PICTURE = T_DEV_DEVICE_TYPE.DEVICE_
CA
TEGORY )
WHERE
id BETWEEN 40000
AND 45907
2.同一张表，用不同字段截取填充另外一字段
UPDATE
T_DEV_ASS
Day 5
更新
语句
文章目录我的
Oracle
数据库
学习笔记修改
语句
1.修改所有男同学的年龄+12.修改年龄在20-30之间的男同学的年龄-13.修改地址为空的学生的年龄+14.修改姓‘王’的同学的年龄+1 ，同时地址改为‘北京’5.修改地址在‘上海’的学生年龄+1提交数据
修改
语句
update
表名 set 列名=新值 [,列名=新值，列名=新值] [where 修改条件]
2.1）
update
语句
不带有where 条件，则表示修改表中
update
student.stuinfo t
set t.age = '24', t.idnumber = '3503021994XXXXXXXX'
where t.stuname = '张三';
commit;
二：
update
利用另外一张表关联
更新
本表数据的命令结构如下：
update
表1set列名...
SQL
的
update
语句
怎么写
UPDATE
表名称 SET 列名称 = 新值 WHERE 列名称 = 某值，
update
语句
的写法：1、
UPDATE
tab
le
_name2、SET column1=value1,column2=value2,。3、WHERE column(1)=value(1),column(2)=value(2)。and column(n)=value(n);4、
UPDATE
...
1.
更新
单行数据
实例1：在学生表中，将学生编号Sid为6的学生姓名Sname修改为：杨红梅
update
students set Sname='杨红梅' where Sid = 6;
执行结果：
SET后面除了带文本之外，还可以是数学表达式
实例2：在学生表中，将学生编号Sid为10的学生姓名对应的Tid
--将product表中regist_date项全部改为2009-10-10
2、
更新
数据时也可以像de
le
te
语句
那样使用where子句，这种指定
更新
对象的
update
语句
称为搜索型
update
语句
update
<表名>
set <列名>
create tab
le
a(no number notnull,name varchar(10) notnull, locvarchar(10) notnull);create tab
le
b( no number notnull, name varchar(10) notnull, loc varchar(10) notnull);insert into a values(10, 'A...
DMLinsert关键字作用：往表中插入一条（多条）数据语法1：元祖值式的插入语法1：insert into tab
le
name(column1,column2,...,columnN) values(value1,value2,...,valueN);例：insert into emp(empno,ename,sal,deptno) values(9526,'hua%an',100,40);语法...
