select * into #tmp_tb from your_tbupdate a set a.col1=b.col1,a.col2=b.col2,... from your_tb a ,#tmp_tb b where ...drop table #tmp_tb
将tb表id=1的记录复制到id=2的记录上:update tb set f1=b.f1, f2=b.f2, f3=b.f3 from tb a inner join (select 2 as bid,* from tb where id=1) b on a.id=b.bid
update b set b.col1=a.col1, b.col2=a.col2, b.col3=a.col3, b.col4=a.col4, b.col5=a.col5, b.col6=a.col6 from tb a, tb where a.xx=b.yy --即两条记录之间的关联关系
update b set b.col1=a.col1, b.col2=a.col2, b.col3=a.col3, b.col4=a.col4, b.col5=a.col5, b.col6=a.col6 from tb a, tb b where a.xx=b.yy --即两条记录之间的关联关系
update tb2 set col1 = tb1.col, col2 = tb1.co2, col3 = tb1.co3, col4 = tb1.co4, col5 = tb1.co5, col6 = tb1.co6 from tb2 , tb1 where tb2.关键字 = tb1.关键字
先谢谢上面热心帮忙的朋友,可能是我问的不清楚,上面几位大侠给的代码有点不懂,本人刚学SQL,汗 我看上面的代码好像用到2个表,而且我也不能建临时表, 我想要的不是表之间的复制,而是同一个表里的记录, 我现在的代码是这样写的: update article set data=(select data from article where NAME='某某') where NAME='新人' update article set link=(select link from article where NAME='某某') where NAME='新人' update article set job=(select job from article where NAME='某某') where NAME='新人'类似这种,怎么合成一句SQL语句?
update article set data=(select data from article where NAME='某某') where NAME='新人' update article set link=(select link from article where NAME='某某') where NAME='新人' update article set job=(select job from article where NAME='某某') where NAME='新人' 你这是什么需求哦?你给出测试数据和结果呢?
我需要按照事先设定好的模板,复制一份给新VIP用户 字段很多。
现在用的数据库,字段很多, 比如: ID NAME COL1 COL2 COL3 ............. 1 模板 info1 data2 data3 .............. 2 张 拷贝 拷贝 拷贝 ..............就是把模板的记录复制一份给张
update codeupdate set data=b.data, link=b.link, job=b.job from codeupdate a inner join (select '新人' as bname,* from codeupdate where name='某某') b on a.name=b.bname 这样可以的, 试试!
哈哈 update article set data=(select data from article where NAME='某某'),link=(select link from article where NAME='某某'),..... where NAME='新人'
--把原表的记录复制一张新表 select * into newtablename from yourname
set a.col1=b.col1,a.col2=b.col2,...
from your_tb a ,#tmp_tb b
where ...drop table #tmp_tb
from tb a
inner join (select 2 as bid,* from tb where id=1) b on a.id=b.bid
set b.col1=a.col1,
b.col2=a.col2,
b.col3=a.col3,
b.col4=a.col4,
b.col5=a.col5,
b.col6=a.col6
from tb a, tb
where a.xx=b.yy --即两条记录之间的关联关系
update b
set b.col1=a.col1,
b.col2=a.col2,
b.col3=a.col3,
b.col4=a.col4,
b.col5=a.col5,
b.col6=a.col6
from tb a, tb b
where a.xx=b.yy --即两条记录之间的关联关系
update tb2
set col1 = tb1.col,
col2 = tb1.co2,
col3 = tb1.co3,
col4 = tb1.co4,
col5 = tb1.co5,
col6 = tb1.co6
from tb2 , tb1
where tb2.关键字 = tb1.关键字
我看上面的代码好像用到2个表,而且我也不能建临时表,
我想要的不是表之间的复制,而是同一个表里的记录,
我现在的代码是这样写的:
update article set data=(select data from article where NAME='某某') where NAME='新人'
update article set link=(select link from article where NAME='某某') where NAME='新人'
update article set job=(select job from article where NAME='某某') where NAME='新人'类似这种,怎么合成一句SQL语句?
update article set link=(select link from article where NAME='某某') where NAME='新人'
update article set job=(select job from article where NAME='某某') where NAME='新人'
你这是什么需求哦?你给出测试数据和结果呢?
字段很多。
比如:
ID NAME COL1 COL2 COL3 .............
1 模板 info1 data2 data3 ..............
2 张 拷贝 拷贝 拷贝 ..............就是把模板的记录复制一份给张
update codeupdate set data=b.data, link=b.link, job=b.job
from codeupdate a
inner join (select '新人' as bname,* from codeupdate where name='某某') b on a.name=b.bname
这样可以的, 试试!
哈哈
update article set data=(select data from article where NAME='某某'),link=(select link from article where NAME='某某'),..... where NAME='新人'
select * into newtablename from yourname