34,590
社区成员
发帖
与我相关
我的任务
分享
Update tb1 set C1=0
from tb1 t1,tb2 t2
where t1.ID=t2.ID and t2.C2>0
Update tb1 set C1=0 where exists (select 1 from tb2 where ID=tb1.ID and C2>0)
Update t1 set t1.C1=0
from tb1 t1,tb2 t2
where t1.ID=t2.ID and t2.C2>0
CREATE TYPE dbo.Type1 AS TABLE
(
Id INT NOT NULL,
C2 INT NOT NULL,
RowID INT NOT NULL IDENTITY,
INDEX ix_RowID HASH(RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @tabvar1 dbo.Type1;
INSERT @tabvar1
(
Id,
c2
)
SELECT Id,
c2
FROM tb2
WHERE C2=0;
DECLARE @i INT = 1,
@Id INT,
@max INT
select @max=max(RowID) from @tabvar1
WHILE @i <= @max
BEGIN
SELECT @Id = Id
FROM @tabvar1
WHERE RowID = @i;
UPDATE tb1
SET c1 = 0
WHERE Id = @Id;
SET @i += 1;
END
[/quote]
报这个错:
Cannot schema bind procedure 'dbo.sp_Hekaton' because name 'tb2' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
你测试能通过?
ALTER proc [dbo].[sp_Hekaton]
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
-- 你把代码写在里面试试?
END
[/quote]
上面那些是我从https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/implementing-update-with-from-or-subqueries看到的,然后我改了下,应该是可以得啊。还有你上面这句
ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
,我也看到了这个,但是不知道是干嘛用的,就删除了。上面这句是干嘛用的,加上上面那句也不行吗?没有道理的啊。按照微软说的,这样是可以的啊[/quote]
表名前加上dbo.就不会报错了。
这个方法可行,就是zengertao(繁花尽流年)说的循环逐条更新。
CREATE TYPE dbo.Type1 AS TABLE
(
Id INT NOT NULL,
C2 INT NOT NULL,
RowID INT NOT NULL IDENTITY,
INDEX ix_RowID HASH(RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @tabvar1 dbo.Type1;
INSERT @tabvar1
(
Id,
c2
)
SELECT Id,
c2
FROM tb2
WHERE C2=0;
DECLARE @i INT = 1,
@Id INT,
@max INT
select @max=max(RowID) from @tabvar1
WHILE @i <= @max
BEGIN
SELECT @Id = Id
FROM @tabvar1
WHERE RowID = @i;
UPDATE tb1
SET c1 = 0
WHERE Id = @Id;
SET @i += 1;
END
[/quote]
报这个错:
Cannot schema bind procedure 'dbo.sp_Hekaton' because name 'tb2' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
你测试能通过?
ALTER proc [dbo].[sp_Hekaton]
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
-- 你把代码写在里面试试?
END
[/quote]
上面那些是我从https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/implementing-update-with-from-or-subqueries看到的,然后我改了下,应该是可以得啊。还有你上面这句
ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
,我也看到了这个,但是不知道是干嘛用的,就删除了。上面这句是干嘛用的,加上上面那句也不行吗?没有道理的啊。按照微软说的,这样是可以的啊
CREATE TYPE dbo.Type1 AS TABLE
(
Id INT NOT NULL,
C2 INT NOT NULL,
RowID INT NOT NULL IDENTITY,
INDEX ix_RowID HASH(RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @tabvar1 dbo.Type1;
INSERT @tabvar1
(
Id,
c2
)
SELECT Id,
c2
FROM tb2
WHERE C2=0;
DECLARE @i INT = 1,
@Id INT,
@max INT
select @max=max(RowID) from @tabvar1
WHILE @i <= @max
BEGIN
SELECT @Id = Id
FROM @tabvar1
WHERE RowID = @i;
UPDATE tb1
SET c1 = 0
WHERE Id = @Id;
SET @i += 1;
END
[/quote]
报这个错:
Cannot schema bind procedure 'dbo.sp_Hekaton' because name 'tb2' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
你测试能通过?
ALTER proc [dbo].[sp_Hekaton]
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
-- 你把代码写在里面试试?
END
CREATE TYPE dbo.Type1 AS TABLE
(
Id INT NOT NULL,
C2 INT NOT NULL,
RowID INT NOT NULL IDENTITY,
INDEX ix_RowID HASH(RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @tabvar1 dbo.Type1;
INSERT @tabvar1
(
Id,
c2
)
SELECT Id,
c2
FROM tb2
WHERE C2=0;
DECLARE @i INT = 1,
@Id INT,
@max INT
SELECT
WHILE @i <= @max
BEGIN
SELECT @Id = Id
FROM @tabvar1
WHERE RowID = @i;
UPDATE tb1
SET c1 = 0
WHERE Id = @Id;
SET @i += 1;
END
UPDATE tb1 SET c1=0
FROM tb2
where tb1.id=tb2.id AND tb2.c2>0
试试[/quote]
还是不行,一样的错误:本机编译的模块 不支持在 UPDATE 语句中使用 FROM 子句或在 DELETE 语句中指定表源。[/quote]
UPDATE tb1 SET c1=0
where exists (select 1 from tb2 where tb1.id=tb2.id and tb2.c2>0)
again?[/quote]
这个就是我的方法二,报错:仅包含 本机编译的模块 的 SELECT 语句支持子查询(即,嵌套在其他查询内的查询)。[/quote]
那想开吧意味着目前不支持批量update,只能逐条update