将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了。
REPLACE(String,from_str,to_str) 即:将String中所有出现的from_str替换为to_str。
CREATE TABLE titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
update titles_test set emp_no=replace(emp_no,10001,10005)
where id=5
and emp_no=10001;
1、查询替换
(1)将 title 里的“Staff”替换成"Senior Engineer"
select *,replace(title,"Staff","Senior Engineer") as newc
from titles_test;
(2)把id=5并且emp_no=10001中的title里的“Senior Engineer”替换成"Manager"
select *,replace(title,"Senior Engineer","Manager") as newc
from titles_test
where id=5
and emp_no=10001;
、2、更新替换。将id=1的emp_no字段里的“10001”替换成“10005”
(局部更新替换)
update titles_test set emp_no=replace(emp_no,10001,10005)
where id=1;
3、插入替换
将id=6的字段from_date字段值改为2000-01-01
replace into titles_test values(6,10003,"Senior Engineer","2000-01-01","9999-01-01");
如果原表中存在id=6的数据,则该插入替换操作就会替换掉原来的id=6的数据(相当于update);如果原表中没有id=6的数据,则该插入替换操作,就作为新数据插入(相当于insert into)