---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-02-28 11:32:49 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[table1] if object_id('[table1]') is not null drop table [table1] go create table [table1]([id] varchar(40),[name] int) insert [table1] select 'aa',1 union all select 'b',2 union all select 'ccc',3 union all select 'dd',4 union all select 'eeee',5 --> 测试数据:[table2] if object_id('[table2]') is not null drop table [table2] go create table [table2]([id] int,[name] varchar(70)) insert [table2] select 1,'aacdf' union all select 2,'eeeesdf' union all select 3,'ddgret' union all select 4,'bgfder' union all select 5,'ccc234' --------------开始查询-------------------------- update b set name=ltrim(a.name)+replace(b.name,a.id,'') from table1 a join table2 b on a.name=b.id
select * from table2 ----------------结果---------------------------- /* id name ----------- ---------------------------------------------------------------------- 1 1cdf 2 2eeeesdf 3 3ddgret 4 4bgfder 5 5ccc234 */
小F姐姐的 update ......from......这个方法经常使用
虽然俺不会,但是还是能看懂LZ的意思的,小F的结果貌似与LZ要求的不一样,t2里的id5 name 字段应该是将ccc替换成t1中的id字段3才对。
--> 测试数据:[table1] if object_id('[table1]') is not null drop table [table1] go create table [table1]([id] varchar(40),[name] int) insert [table1] select 'aa',1 union all select 'b',2 union all select 'ccc',3 union all select 'dd',4 union all select 'eeee',5 --> 测试数据:[table2] if object_id('[table2]') is not null drop table [table2] go create table [table2]([id] int,[name] varchar(70)) insert [table2] select 1,'aacdf' union all select 2,'eeeesdf' union all select 3,'ddgret' union all select 4,'bgfder' union all select 5,'ccc234' update b set name=stuff(b.name,1,len(a.id),ltrim(a.name)) from table1 a join table2 b on b.name like a.id+'%'
select * from table2 /* id name ----------- ---------------------------------------------------------------------- 1 1cdf 2 5sdf 3 4gret 4 2gfder 5 3234(所影响的行数为 5 行)
update b set name=ltrim(a.name)+replace(b.name,a.id,'') from table1 a join table2 b on b.name like a.id+'%'那我抄袭一个吧
这样好了update b set name=ltrim(a.name)+replace(b.name,a.id,'') from table1 a , table2 b where charindex(a.id,b.name)>0
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-02-28 11:32:49
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] varchar(40),[name] int)
insert [table1]
select 'aa',1 union all
select 'b',2 union all
select 'ccc',3 union all
select 'dd',4 union all
select 'eeee',5
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[name] varchar(70))
insert [table2]
select 1,'aacdf' union all
select 2,'eeeesdf' union all
select 3,'ddgret' union all
select 4,'bgfder' union all
select 5,'ccc234'
--------------开始查询--------------------------
update
b
set
name=ltrim(a.name)+replace(b.name,a.id,'')
from
table1 a join table2 b
on
a.name=b.id
select * from table2
----------------结果----------------------------
/* id name
----------- ----------------------------------------------------------------------
1 1cdf
2 2eeeesdf
3 3ddgret
4 4bgfder
5 5ccc234
*/
小F姐姐的
update ......from......这个方法经常使用
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] varchar(40),[name] int)
insert [table1]
select 'aa',1 union all
select 'b',2 union all
select 'ccc',3 union all
select 'dd',4 union all
select 'eeee',5
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[name] varchar(70))
insert [table2]
select 1,'aacdf' union all
select 2,'eeeesdf' union all
select 3,'ddgret' union all
select 4,'bgfder' union all
select 5,'ccc234'
update
b
set
name=stuff(b.name,1,len(a.id),ltrim(a.name))
from
table1 a join table2 b
on
b.name like a.id+'%'
select * from table2
/*
id name
----------- ----------------------------------------------------------------------
1 1cdf
2 5sdf
3 4gret
4 2gfder
5 3234(所影响的行数为 5 行)
b
set
name=ltrim(a.name)+replace(b.name,a.id,'')
from
table1 a join table2 b
on
b.name like a.id+'%'那我抄袭一个吧
b
set
name=ltrim(a.name)+replace(b.name,a.id,'')
from
table1 a , table2 b
where
charindex(a.id,b.name)>0