有A,B表,每个表各50记录,A表中有makeid字段是nchar(30),B表中有id字段是nchar(40),现在我想用A中的makeid更新 B中的id字段{注意A,B都不只有一个字段。}{不要遍历更改的,只要SQL的}我写的update B set id= (select makeid from A) 为什么不行呢?哪位给改改
解决方案 »
- 怎么修改fd数据库中表user里面的id为1的管理员的密码?
- 新手写一个触发器的问题?
- 如何查询带中括号的字段,如:发[2006]5号
- 求助啊,这到底是为什么呢?
- sql数据库复制、备份、恢复问题
- 1月1日的老问题,跪求各位大虾解决,解决了我请吃饭。
- 求SQL语句一条
- 表的关联问题?
- SELECT B.text FROM sysobjects A INNER JOIN syscomments B ON A.id = B.id
- 数据库问题:[ODBC SQL Server Driver]连接占线导致另一个hstmt什么意思?
- 让哥蛋疼的cast函数
- 这种语法是什么意思??CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
select makeid from A 会返回很多条记录,哪你到底id 更新那个值?update b
set b.id= a.makeid
from B b,A a
where 要更新时A,B表符合的条件
2005可以直接用row_number() 函数直接实现
update B set id= (select makeid from A)
--不关联怎么行呢?每个B表中的id怎么可以用50个A表中的id赋值呢?
set
id=n.makeid
from
B m,A n
where
(select count(1) from B where id<=m.id)=(select count(1) from A where makeid<=n.makeid)
alter table ta add rid int identity(1,1)
go
alter table tb add rid int identity(1,1)
go
update b
set id = a.makeid
from ta a,tb b
where a.rid = b.ridselect * from tb
goalter table ta drop column rid
go
alter table tb drop column rid
go
-- Author:happyflystone
-- Version:V1.001
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(makeid nvarchar(30))
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(id nvarchar(40))
Go
declare @s int
set @s = 1
while @s <= 50
begin
Insert into ta
select @s
set @s = @s + 1
endGo
insert into tb select '' from ta
--Startalter table ta add rid int identity(1,1)
go
alter table tb add rid int identity(1,1)
go--update
update b
set id = a.makeid
from ta a,tb b
where a.rid = b.ridselect * from tb
goalter table ta drop column rid
go
alter table tb drop column rid
go--Result:
/*
*/
--End
问题我重新表述了一下。
一共150分了
http://topic.csdn.net/u/20090409/10/2b7d793d-03e3-4ccd-84b4-30dc66455855.html?seed=1560818706
--或者利用你的数据的规律
create table tablea(aid nchar(10),aname nchar(50))create table tableb(bid nchar(10),bname nchar(50),bcontext nchar(80) )
update tablea set aid = b.bid from tablea a,tableb b
where replace(replace(a.aid,'ab',''),'ef','')=replace(replace(b.bid,'ab',''),'ef','')-350
and replace(replace(b.bid,'ab',''),'ef','')>=350
select * from tablea
update A set A.aid= from tablea,
(select * from (select top 200 *,row_number() over(order by bid desc) rank from tableb)
where rank>50)B where A.aid=B.bid你的数据规律是什么?
aid和bid怎么能关联上?
aid和bid是从小到大排序吗?
bid的50-250数据和aid是一致的吗?
一个用20行数据,一个用30行数据
update rpt_temp_tablea t1set t1.aid=(select t2.bid from
(select a.*,bb.* from(select a.*, to_number(replace(aname,'B-','')) x1,rownum x2 from rpt_temp_tablea a order by to_number(replace(aname,'B-',''))) a,(
select
b.bid,b.bname,b.x1 xx1,rownum xx2 from
(select b.* ,to_number(replace(bname,'B-','')) x1,rownum x2 from rpt_temp_tableb b order by to_number(replace(bname,'B-','')) ) b
where
b.x2 between 5 and 25) bb
where a.x2=bb.xx2) t2 where to_number(replace(t1.aname,'B-',''))=t2.xx2)--验证
select * from rpt_temp_tablea--数据(仅构造了20条,和30条的两个表)
AB35EF,B-1
AB36EF,B-2
AB37EF,B-3
AB38EF,B-4
AB39EF,B-5
AB40EF,B-6
AB41EF,B-7
AB42EF,B-8
AB43EF,B-9
AB44EF,B-10
AB45EF,B-11
AB46EF,B-12
AB47EF,B-13
AB48EF,B-14
AB49EF,B-15
AB50EF,B-16
AB51EF,B-17
AB52EF,B-18
AB53EF,B-19
AB54EF,B-20
USING (SELECT ID , VAL
FROM TB) A
ON (GA.ID = A.ID)
WHEN MATCHED THEN
UPDATE SET GA.VAL= A.VAL;