use tempdb;
GO
if(object_id('torder','U') is not null)
drop table torder;
create table torder(
id bigint not null identity(1,1) constraint PK_TORDER primary key,
onumb varchar(10) not null,
odetailnumb varchar(20) not null,
val1 decimal(20,8),
val2 decimal(20,8),
constraint UN_TORDER unique nonclustered(onumb)
);
if(object_id('torderUp','U') is not null)
drop table torderUp
create table torderUp(
id bigint not null identity(1,1) constraint PK_torderUp primary key,
onumb varchar(10) not null,
odetailnumb varchar(20) not null,
val1 decimal(20,8)
constraint UN_torderUp unique nonclustered(onumb)
);
insert into torder (onumb,odetailnumb,val1,val2) values
('111','AAAAAAAA',10.5,15.5),('222','2222222222',20.5,25.5);
insert into torderUp (onumb,odetailnumb,val1) values
('111','AAAAAAAA',11.11),('222','BBBBBBBBB',12.22);
select * from torder a join torderUp b on a.onumb=b.onumb
--复合赋值运算符更新（+=，-+，*=，/=,%=）
update torder set val2+=100;
UPDATE torder set val1+=10000,val2+=val1;
UPDATE torder set val1=val2,val2=val1;
UPDATE a
SET odetailnumb = b.odetailnumb,val1=b.val1
FROM torder a JOIN torderUp b ON a.onumb = b.onumb;
UPDATE torder
SET odetailnumb =
(SELECT b.odetailnumb
FROM torderUp b
WHERE torder.onumb = b.onumb),
val1 =
(SELECT b.val1
FROM torderUp b
WHERE torder.onumb = b.onumb)
WHERE EXISTS
(SELECT 1
FROM torderUp b
WHERE torder.onumb = b.onumb);
UPDATE a
SET odetailnumb =
(SELECT b.odetailnumb
FROM torderUp b
WHERE a.onumb = b.onumb),
val1 =
(SELECT b.val1
FROM torderUp b
WHERE a.onumb = b.onumb)
FROM torder a /* 表别名 */
WHERE EXISTS
(SELECT 1
FROM torderUp b
WHERE a.onumb = b.onumb);
declare @t decimal(20,8)=NULL;
update torder set @t=val1=val2+1;
select @t;
begin tran;
UPDATE a
SET odetailnumb = b.odetailnumb,val1=b.val1
FROM torder a JOIN torderUp b ON a.onumb = b.onumb;
select * FROM torder a JOIN torderUp b ON a.onumb = b.onumb;
rollback;
begin tran;
;with CTE as
(
select a.odetailnumb, b.odetailnumb as odNew, a.val1, b.val1 as val1New
FROM torder a JOIN torderUp b ON a.onumb = b.onumb)
update cte set odetailnumb=odNew,val1=val1New;
select * FROM torder a JOIN torderUp b ON a.onumb = b.onumb;
rollback;
update D set odetailnumb=odNew,val1=val1New
from (select a.odetailnumb, b.odetailnumb as odNew, a.val1, b.val1 as val1New
FROM torder a JOIN torderUp b ON a.onumb = b.onumb) AS D;
update D set onumb=rn
from
(select onumb,row_number() over(order by (select 1)) as rn from torder) as D;
select * from torder;
drop table torder;
drop table torderup;
select t*10+o col ,newid() id into tnum
from
(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as D(t)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as E(o);
update D set col=numb
from(select col,id,row_number() over(order by id) numb from tnum) as D ;
update D set id=NULL from (
select top 10 col,id from tnum order by col) AS D;
select * from tnum order by col;
drop table tnum;
