update t1 set column2=t2.column1 from t1 inner join t2 on right(t1.column2,1)=right(t2.column1)select * from t1
谢谢两位! 但是我的表中的数据是没有关联的!所以用 update t1 set column2=t2.column1 from t1 inner join t2 on right(t1.column2,1)=right(t2.column1) 不可以! 有不相等的情况,我举的例子正好是可以做等于的,但是实际情况是不可以做等于的。是不是仅用简单的sql语句确实无法实现此功能阿?
OrUpdate table_1 set column2=table_1.column1 from table_1 ,table_1 Where right(table_1.column2,1)=right(table_2.column1)
--测试数据(方法是在临时表中插入序号列) if object_id('table_1') is not null drop table table_1 go create table table_1(column1 varchar(10), column2 varchar(10)) insert table_1 select 'a1', 'b1' union all select 'a2', 'b2' union all select 'a3', 'b3' if object_id('table_2') is not null drop table table_2 go create table table_2(column1 varchar(10)) insert table_2 select 'c1' union all select 'c2' union all select 'c3' union all select 'c4' union all select 'c5' --创建临时表 select * , id=identity(int) into #t1 from table_1 select * , id=identity(int) into #t2 from table_2 --修改#t1,得到想要的结果 update #t1 set column2=#t2.column1 from #t2 where #t1.id= #t2.id --查看结果 select * from #t1 --扫尾 drop table #t1 drop table #t2 drop table table_1 drop table table_2
set column2=t2.column1
from t1 inner join t2 on right(t1.column2,1)=right(t2.column1)select * from t1
但是我的表中的数据是没有关联的!所以用
update t1
set column2=t2.column1
from t1 inner join t2 on right(t1.column2,1)=right(t2.column1)
不可以!
有不相等的情况,我举的例子正好是可以做等于的,但是实际情况是不可以做等于的。是不是仅用简单的sql语句确实无法实现此功能阿?
from table_1 ,table_1 Where right(table_1.column2,1)=right(table_2.column1)
if object_id('table_1') is not null drop table table_1
go
create table table_1(column1 varchar(10), column2 varchar(10))
insert table_1 select 'a1', 'b1'
union all select 'a2', 'b2'
union all select 'a3', 'b3'
if object_id('table_2') is not null drop table table_2
go
create table table_2(column1 varchar(10))
insert table_2 select 'c1'
union all select 'c2'
union all select 'c3'
union all select 'c4'
union all select 'c5'
--创建临时表
select * , id=identity(int) into #t1
from table_1
select * , id=identity(int) into #t2
from table_2
--修改#t1,得到想要的结果
update #t1
set column2=#t2.column1
from #t2
where #t1.id= #t2.id
--查看结果
select * from #t1
--扫尾
drop table #t1
drop table #t2
drop table table_1
drop table table_2