更新所有记录 - 没有条件
改变所有[标题]一个SQL表的没有更新所有记录 - 没有条件
我使用了一个循环,但不知道如何影响更新语句中所有行的任何条件
它应该是轻松的一年
类似:
WHILE @counter < 89
BEGIN
Update Products
SET Title = ('Product ' + @counter)
Where Title LIKE ''
SET @counter = @counter + 1 ;
END
SELECT Title FROM Products
Update Products
SET Title = ('Product ' + cast(@counter as varchar)) ;
这将更新所有具有相同值的行
但
,如果你需要一个autonumerated不同的值,试试这个:
Update Products SET
Title = ('Product ' + cast(@counter as varchar)),
@counter = @counter+1 ;
它绝对工作,尝试这
CREATE table t (a VARCHAR(100))
INSERT t VALUES('a')
INSERT t VALUES('a')
INSERT t VALUES('a')
INSERT t VALUES('a')
DECLARE @counter INT = 0
UPDATE t SET a = 'aaa'+CAST(@counter AS VARCHAR), @counter = @counter+1
SELECT * FROM t
输出是
a
----
aaa1
aaa2
aaa3
aaa4
没有工作,没有区别 – Sypress 2012-01-05 09:01:43
向我们展示表格定义和错误或结果 – 2012-01-05 09:14:01
只是删除where条件:
Update Products
SET Title = ('Product ' + @counter) ;
起初我使用这个,但不会工作,在最后'选择'它后总是给我以前的结果集 – Sypress 2012-01-05 08:56:13
只需删除其中 - 科查询和一切都得到更新。
--Test data
DECLARE @Products TABLE(Title Varchar(20))
INSERT INTO @Products (Title) VALUES ('')
INSERT INTO @Products (Title) VALUES ('')
INSERT INTO @Products (Title) VALUES ('')
INSERT INTO @Products (Title) VALUES ('')
INSERT INTO @Products (Title) VALUES ('')
--Query
DECLARE @counter int
SET @counter = 0
UPDATE p
SET Title = ('Product ' + CAST(@counter as varchar)), @counter = @counter+1
FROM @Products p
SELECT Title FROM @Products
尝试下一个查询:
create table #T (id int identity (0,1), Title varchar(10))
INSERT INTO #T (Title) VALUES ('')
INSERT INTO #T (Title) VALUES ('')
INSERT INTO #T (Title) VALUES ('')
INSERT INTO #T (Title) VALUES ('')
INSERT INTO #T (Title) VALUES ('')
INSERT INTO #T (Title) VALUES (NULL)
INSERT INTO #T (Title) VALUES ('xxxxxx')
Update sub
set
Title = 'Product ' + cast(cntr as varchar)
from
(select Title, row_number() OVER (PARTITION BY ISNULL(Title,'') ORDER BY id ASC) as cntr from #T) sub
where ISNULL(Title,'') = ''
select * from #T
所以看来你有解决方案,你要改进的地方? – alexm 2012-01-05 08:50:24
我不认为你甚至需要包含Where子句,因为它会影响所有行,所以更新产品SET title =(whatever); – Andy 2012-01-05 08:51:26
显示使用整个代码,我们将能够重写它,以避免循环;) – onedaywhen 2012-01-05 08:51:41