sql server 更新同一张表数据

Question

是这样吗：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aabb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aabb]
GOCREATE TABLE [dbo].[aabb] (
[bh] [int] NOT NULL ,
[sl] [numeric](18, 0) NULL ,
[mj] [numeric](18, 0) NULL ,
[mk] [numeric](18, 0) NULL ,
[dw] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
insert into [dbo].[aabb]
select 1   ,3    ,1      ,null     ,'ab' union all
select 2   ,4    ,2      ,null     ,'ac' union all
select 3   ,5    ,3      ,null     ,'ad' union all
select 4   ,6    ,null   ,null     ,'ab' union all
select 5   ,7    ,null   ,null     ,'ac' union all
select 6   ,8    ,null   ,null     ,'ac' union all
select 7  , 9    ,null   ,null     ,'ad'
go
--修改成这样
UPDATE B SET MJ=A.Mj
FROM aabb B,aabb A
WHERE B.dw=A.dw  and b.mj is null and a.mj is not null
select * from aabb
/*
bh sl mj mk dw
1 3 1 NULL ab
2 4 2 NULL ac
3 5 3 NULL ad
4 6 1 NULL ab
5 7 2 NULL ac
6 8 2 NULL ac
7 9 3 NULL ad
*/

Answer 1

是的，不过如果把第一行DW为AD的MJ 改为5 第二行的DW是 AC的 MJ改为6 在用这个UPDATE 就不行了 

Answer 2

应为你有where条件啊，你改了这个，匹配不上，你可以先select看结果，在update

Answer 3

条件是  WHERE dw=A.dw
这是一个类似dbgrid的表格，要求是只输入一个值，点击修改，相同dw的价格和金额会自动填充。填充之后，要是有错误，只修改第一条数据，后面的也会自动修改，所以建了AABB表做测试

Answer 4

现在就是想在都有数据的情况下，做二次修改，并且是批量修改再试试这个把：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aabb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aabb]
GOCREATE TABLE [dbo].[aabb] (
[bh] [int] NOT NULL ,
[sl] [numeric](18, 0) NULL ,
[mj] [numeric](18, 0) NULL ,
[mk] [numeric](18, 0) NULL ,
[dw] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
insert into [dbo].[aabb]
select 1   ,3    ,1      ,null     ,'ab' union all
select 2   ,4    ,2      ,null     ,'ac' union all
select 3   ,5    ,3      ,null     ,'ad' union all
select 4   ,6    ,null   ,null     ,'ab' union all
select 5   ,7    ,null   ,null     ,'ac' union all
select 6   ,8    ,null   ,null     ,'ac' union all
select 7  , 9    ,null   ,null     ,'ad'
go
--修改成这样
UPDATE B SET MJ=A.Mj
FROM aabb B
inner join aabb a
        on B.dw=A.dw
inner join 
(
select dw,MAX(bh) bh
from aabb
group by dw 
)c
 on a.dw = c.dw and a.bh = c.bhselect * from aabb
/*
bh sl mj mk dw
1 3 1 NULL ab
2 4 2 NULL ac
3 5 3 NULL ad
4 6 1 NULL ab
5 7 2 NULL ac
6 8 2 NULL ac
7 9 3 NULL ad
*/

Answer 5

好像没啥问题啊----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-02-19 11:25:31
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[aabb]
if object_id('[aabb]') is not null drop table [aabb]
go 
create table [aabb]([bh] int,[sl] int,[mj] int,[mk] sql_variant,[dw] varchar(2))
insert [aabb]
select 1,3,1,null,'ab' union all
select 2,4,2,null,'ac' union all
select 3,5,3,null,'ad' union all
select 4,6,null,null,'ab' union all
select 5,7,null,null,'ac' union all
select 6,8,null,null,'ac' union all
select 7,9,null,null,'ad'
--------------开始查询--------------------------select * from [aabb]UPDATE B SET MJ=A.Mj
FROM aabb B,aabb A
WHERE B.dw=A.dw  
select * from [aabb]UPDATE B SET MJ=A.Mj
FROM aabb B,aabb A
WHERE B.dw=A.dw  select * from [aabb]----------------结果----------------------------
/* 
bh          sl          mj          mk                                                                                                                                                                                                                                                               dw
----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
1           3           1           NULL                                                                                                                                                                                                                                                             ab
2           4           2           NULL                                                                                                                                                                                                                                                             ac
3           5           3           NULL                                                                                                                                                                                                                                                             ad
4           6           NULL        NULL                                                                                                                                                                                                                                                             ab
5           7           NULL        NULL                                                                                                                                                                                                                                                             ac
6           8           NULL        NULL                                                                                                                                                                                                                                                             ac
7           9           NULL        NULL                                                                                                                                                                                                                                                             ad(7 row(s) affected)(7 row(s) affected)bh          sl          mj          mk                                                                                                                                                                                                                                                               dw
----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
1           3           1           NULL                                                                                                                                                                                                                                                             ab
2           4           2           NULL                                                                                                                                                                                                                                                             ac
3           5           NULL        NULL                                                                                                                                                                                                                                                             ad
4           6           NULL        NULL                                                                                                                                                                                                                                                             ab
5           7           2           NULL                                                                                                                                                                                                                                                             ac
6           8           NULL        NULL                                                                                                                                                                                                                                                             ac
7           9           3           NULL                                                                                                                                                                                                                                                             ad(7 row(s) affected)(7 row(s) affected)bh          sl          mj          mk                                                                                                                                                                                                                                                               dw
----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
1           3           1           NULL                                                                                                                                                                                                                                                             ab
2           4           2           NULL                                                                                                                                                                                                                                                             ac
3           5           3           NULL                                                                                                                                                                                                                                                             ad
4           6           NULL        NULL                                                                                                                                                                                                                                                             ab
5           7           2           NULL                                                                                                                                                                                                                                                             ac
6           8           2           NULL                                                                                                                                                                                                                                                             ac
7           9           NULL        NULL                                                                                                                                                                                                                                                             ad*/

Answer 6

现在就是想在都有数据的情况下，做二次修改，并且是批量修改再试试这个把：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aabb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aabb]
GOCREATE TABLE [dbo].[aabb] (
[bh] [int] NOT NULL ,
[sl] [numeric](18, 0) NULL ,
[mj] [numeric](18, 0) NULL ,
[mk] [numeric](18, 0) NULL ,
[dw] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
insert into [dbo].[aabb]
select 1   ,3    ,1      ,null     ,'ab' union all
select 2   ,4    ,2      ,null     ,'ac' union all
select 3   ,5    ,3      ,null     ,'ad' union all
select 4   ,6    ,null   ,null     ,'ab' union all
select 5   ,7    ,null   ,null     ,'ac' union all
select 6   ,8    ,null   ,null     ,'ac' union all
select 7  , 9    ,null   ,null     ,'ad'
go
--修改成这样
UPDATE B SET MJ=A.Mj
FROM aabb B
inner join aabb a
        on B.dw=A.dw
inner join 
(
select dw,MAX(bh) bh
from aabb
group by dw 
)c
 on a.dw = c.dw and a.bh = c.bhselect * from aabb
/*
bh sl mj mk dw
1 3 1 NULL ab
2 4 2 NULL ac
3 5 3 NULL ad
4 6 1 NULL ab
5 7 2 NULL ac
6 8 2 NULL ac
7 9 3 NULL ad
*/
还是不行，我把mj改为3，运行之后自动变成1了。

Answer 7

修改一下：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aabb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aabb]
GOCREATE TABLE [dbo].[aabb] (
[bh] [int] NOT NULL ,
[sl] [numeric](18, 0) NULL ,
[mj] [numeric](18, 0) NULL ,
[mk] [numeric](18, 0) NULL ,
[dw] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
insert into [dbo].[aabb]
select 1   ,3    ,1      ,null     ,'ab' union all
select 2   ,4    ,2      ,null     ,'ac' union all
select 3   ,5    ,3      ,null     ,'ad' union all
select 4   ,6    ,null   ,null     ,'ab' union all
select 5   ,7    ,null   ,null     ,'ac' union all
select 6   ,8    ,null   ,null     ,'ac' union all
select 7  , 9    ,null   ,null     ,'ad'
go
--修改成这样
UPDATE B SET MJ=A.Mj
FROM aabb B
inner join aabb a
        on B.dw=A.dw
inner join 
(
select dw,min(bh) bh
from aabb
where mj is not null
group by dw 
)c
 on a.dw = c.dw and a.bh = c.bhselect * from aabb
/*
bh sl mj mk dw
1 3 1 NULL ab
2 4 2 NULL ac
3 5 3 NULL ad
4 6 1 NULL ab
5 7 2 NULL ac
6 8 2 NULL ac
7 9 3 NULL ad
*/

Answer 8

现在结果是 
1 3  1 3   ab
2 4  2 8   ac
3 5  3 15  ad
4 6  1 6   ab
5 7  2 14  ac
6 8  3 24  ad
7 9  3 24  ad现在把 1 3  1 3   ab 修改为 1 3  5 15   ab
       2 4  2 8   ac 修改为 2 4  6 24   ac
然后怎么批量修改 下面单位是 ab ac的mj