表A id value 1 '' 2 '' 3 ''表B id value 1 '1' 2 '3' 2 '2' 3 '3'update a set value = b.value from b where a.id = b.id这样的语句会把a更新成 1 '1' 2 '2' 3 '3'但是我希望更新成 1 '1' 2 '3' 3 '3'就是希望这个语句 update a set value = b.value from b where a.id = b.id 从b中取数据的时候能根据value的大小做个排序按找从小到大的顺序
update a set value = C.value from ( select id,max(value) from b group by id ) C where a.id = C.id
UPDATE A SET A.value=B.value FROM A,(SELECT ID,MAX(value) AS value FROM B GROUP BY ID) AS B WHERE A.ID=B.ID
--> 测试数据: [A] if object_id('[A]') is not null drop table [A] create table [A] (id int,value sql_variant) insert into [A] select 1,null union all select 2,null union all select 3,null --> 测试数据: [B] if object_id('[B]') is not null drop table [B] create table [B] (id int,value int) insert into [B] select 1,1 union all select 2,3 union all select 2,2 union all select 3,3update a set value = t.value from b t where a.id = t.id and not exists(select 1 from b where id=t.id and value>t.value)
--如果要排序,只能这样。update a set value = t.value from (select top 100 percent * from b order by id,value) t where a.id = t.id
declare @A table(id int, value varchar(10)) insert @a select 1, '' insert @a select 2, '' insert @a select 3, '' declare @B table(id int, value varchar(10)) insert @B select 1, '1' insert @B select 2, '3' insert @B select 2, '2' insert @B select 3, '3' update a set value=t.value from @a a inner join( select * from @B b where not exists(select 1 from @b where id=b.id and value>b.value))t on t.id=a.id select * from @a /* id value ----------- ---------- 1 1 2 3 3 3 */
IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE [A] CREATE TABLE [A] (ID INT,VALUE NVARCHAR(50)) INSERT INTO [A] SELECT 1,NULL UNION ALL SELECT 2,NULL UNION ALL SELECT 3,NULLIF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B] CREATE TABLE [B] (ID INT,VALUE NVARCHAR(50)) INSERT INTO [B] SELECT 1,1 UNION ALL SELECT 2,3 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3UPDATE [A] SET VALUE = [C].VALUE FROM (SELECT ID,MAX(VALUE) AS VALUE FROM [B] GROUP BY ID) [C] WHERE [A].ID=[C].IDSELECT * FROM [A]DROP TABLE [A] DROP TABLE [B] /* ID VALUE ----------- -------------------------------------------------- 1 1 2 3 3 3(3 個資料列受到影響)*/
update a set value = C.value from ( select id,max(value) from b group by id ) C where a.id = C.id
表A id value 1 0 2 0 3 0 表B id value Type 1 1 0 2 3 1 2 2 1 3 3 0Type = 0 表示改变的方式为累加 1为修改update a set value = case b.Type when 0 then Value + b.Value when 1 then b.Value else Value end b.value from b where a.id = b.id 这样的语句会把a更新成 1 1 2 2 3 3 但是我希望更新成 1 1 2 3 3 3 就是希望这个语句 从b中取数据的时候能根据value的大小做个排序按找从小到大的顺序
if object_id('A')is not null drop table A if object_id('B')is not null drop table B go create table A (id int, value varchar(10)) insert into A select 1,'' union all select 2,'' union all select 3,'' go create table B(id int, value varchar(10)) insert into B select 1,'1' union all select 2,'3' union all select 2,'2' union all select 3,'3'-------------- --update A set A.value= c.value from B c where not exists(select * from B where value>c.value and id=c.id) --select * from A ------- --select * from B c where exists(select * from B where value>c.value and id=c.id) delete c from B c where exists(select * from B where value>c.value and id=c.id) ------------ --select * from B update A set A.value=B.value from A join B on a.id=b.id select * from A 1 1 2 3 3 3
--2000 下可以这样 --2005 这样写不一定有效果. if object_id('[A]') is not null drop table [A] create table [A] (id int,value sql_variant) insert into [A] select 1,null union all select 2,null union all select 3,null if object_id('[B]') is not null drop table [B] create table [B] (id int,value int) insert into [B] select 1,1 union all select 2,3 union all select 2,2 union all select 3,3 update a set value=b.value from a,( select top 100 percent * from b order by id,value ) b where a.id=b.id
--> liangCK小梁 于2008-10-21 --> 生成测试数据: #tb1 IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1 CREATE TABLE #tb1 (id INT,value INT) INSERT INTO #tb1 SELECT 1,0 UNION ALL SELECT 2,0 UNION ALL SELECT 3,0 --> liangCK小梁 于2008-10-21 --> 生成测试数据: #tb2 IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2 CREATE TABLE #tb2 (id INT,value INT,Type INT) INSERT INTO #tb2 SELECT 1,1,0 UNION ALL SELECT 2,3,1 UNION ALL SELECT 2,2,1 UNION ALL SELECT 3,3,0--SQL查询如下:UPDATE #tb1 SET value=CASE WHEN EXISTS(SELECT * FROM #tb2 WHERE id=#tb1.id AND Type=0) THEN (SELECT value FROM #tb2 WHERE id=#tb1.id) ELSE (SELECT SUM(Type) FROM #tb2 WHERE id=#tb1.id) END;SELECT * FROM #tb1;/* id value ----------- ----------- 1 1 2 2 3 3(3 行受影响)*/
--是不是这样?--生成测试数据 select 1 [id],0 value into #t union select 2,0 union select 3,0 select 1 [id],1 value,0 type into #t2 union select 2,3,1 union select 2,2,1 union select 3,3,0 --更新语句 update a set a.value = case b.Type when 0 then a.Value + b.Value when 1 then b.Value else a.Value end from #t a,(select [id],max(value) value,type from #t2 group by [id],[type]) b where a.[id] = b.[id] select * from #t drop table #t,#t2/*结果 id value ----------- ----------- 1 1 2 3 3 3 */
id value
1 ''
2 ''
3 ''表B
id value
1 '1'
2 '3'
2 '2'
3 '3'update a set value = b.value from b where a.id = b.id这样的语句会把a更新成
1 '1'
2 '2'
3 '3'但是我希望更新成
1 '1'
2 '3'
3 '3'就是希望这个语句
update a set value = b.value from b where a.id = b.id
从b中取数据的时候能根据value的大小做个排序按找从小到大的顺序
update a set value = C.value from
(
select id,max(value) from b group by id
) C
where a.id = C.id
SET A.value=B.value
FROM A,(SELECT ID,MAX(value) AS value FROM B GROUP BY ID) AS B
WHERE A.ID=B.ID
if object_id('[A]') is not null drop table [A]
create table [A] (id int,value sql_variant)
insert into [A]
select 1,null union all
select 2,null union all
select 3,null
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (id int,value int)
insert into [B]
select 1,1 union all
select 2,3 union all
select 2,2 union all
select 3,3update a set value = t.value from b t where a.id = t.id
and not exists(select 1 from b where id=t.id and value>t.value)
------------------------------------------------------ 这个不应该从顺序上考虑吧? 反正最终保留的只有一条, 你先把这一条查出来, 再关联更新不就行了吗?
insert @a select 1, ''
insert @a select 2, ''
insert @a select 3, ''
declare @B table(id int, value varchar(10))
insert @B select 1, '1'
insert @B select 2, '3'
insert @B select 2, '2'
insert @B select 3, '3'
update a set value=t.value from @a a inner join(
select * from @B b where not exists(select 1 from @b where id=b.id and value>b.value))t on t.id=a.id
select * from @a
/*
id value
----------- ----------
1 1
2 3
3 3
*/
CREATE TABLE [A] (ID INT,VALUE NVARCHAR(50))
INSERT INTO [A]
SELECT 1,NULL UNION ALL
SELECT 2,NULL UNION ALL
SELECT 3,NULLIF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B]
CREATE TABLE [B] (ID INT,VALUE NVARCHAR(50))
INSERT INTO [B]
SELECT 1,1 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3UPDATE [A]
SET VALUE = [C].VALUE
FROM (SELECT ID,MAX(VALUE) AS VALUE FROM [B] GROUP BY ID) [C]
WHERE [A].ID=[C].IDSELECT * FROM [A]DROP TABLE [A]
DROP TABLE [B]
/*
ID VALUE
----------- --------------------------------------------------
1 1
2 3
3 3(3 個資料列受到影響)*/
(
select id,max(value) from b group by id
) C
where a.id = C.id
id value
1 0
2 0
3 0 表B
id value Type
1 1 0
2 3 1
2 2 1
3 3 0Type = 0 表示改变的方式为累加 1为修改update a set value =
case b.Type when 0 then Value + b.Value
when 1 then b.Value else Value end
b.value from b where a.id = b.id 这样的语句会把a更新成
1 1
2 2
3 3 但是我希望更新成
1 1
2 3
3 3 就是希望这个语句
从b中取数据的时候能根据value的大小做个排序按找从小到大的顺序
if object_id('A')is not null
drop table A
if object_id('B')is not null
drop table B
go
create table A (id int, value varchar(10))
insert into A select 1,''
union all select 2,''
union all select 3,''
go
create table B(id int, value varchar(10))
insert into B select 1,'1'
union all select 2,'3'
union all select 2,'2'
union all select 3,'3'--------------
--update A set A.value= c.value from B c where not exists(select * from B where value>c.value and id=c.id)
--select * from A
-------
--select * from B c where exists(select * from B where value>c.value and id=c.id)
delete c from B c where exists(select * from B where value>c.value and id=c.id)
------------
--select * from B
update A set A.value=B.value from A join B on a.id=b.id
select * from A
1 1
2 3
3 3
--2000 下可以这样
--2005 这样写不一定有效果.
if object_id('[A]') is not null drop table [A]
create table [A] (id int,value sql_variant)
insert into [A]
select 1,null union all
select 2,null union all
select 3,null
if object_id('[B]') is not null drop table [B]
create table [B] (id int,value int)
insert into [B]
select 1,1 union all
select 2,3 union all
select 2,2 union all
select 3,3 update a set value=b.value
from a,(
select top 100 percent *
from b
order by id,value
) b
where a.id=b.id
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (id INT,value INT)
INSERT INTO #tb1
SELECT 1,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 3,0
--> liangCK小梁 于2008-10-21
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (id INT,value INT,Type INT)
INSERT INTO #tb2
SELECT 1,1,0 UNION ALL
SELECT 2,3,1 UNION ALL
SELECT 2,2,1 UNION ALL
SELECT 3,3,0--SQL查询如下:UPDATE #tb1
SET value=CASE WHEN EXISTS(SELECT *
FROM #tb2
WHERE id=#tb1.id
AND Type=0)
THEN (SELECT value
FROM #tb2
WHERE id=#tb1.id)
ELSE
(SELECT SUM(Type)
FROM #tb2
WHERE id=#tb1.id)
END;SELECT * FROM #tb1;/*
id value
----------- -----------
1 1
2 2
3 3(3 行受影响)*/
select 1 [id],0 value into #t union
select 2,0 union
select 3,0 select 1 [id],1 value,0 type into #t2 union
select 2,3,1 union
select 2,2,1 union
select 3,3,0
--更新语句
update a set a.value =
case b.Type when 0 then a.Value + b.Value when 1 then b.Value else a.Value end
from #t a,(select [id],max(value) value,type from #t2 group by [id],[type]) b
where a.[id] = b.[id] select * from #t
drop table #t,#t2/*结果
id value
----------- -----------
1 1
2 3
3 3
*/