为什么刚添加一列，就updata就报错？

[ahui_net]

alter table _Crm_Company Add [CustomCode] nvarchar(50) not null default ''
update _Crm_Company set CustomCode = EnglishName

会报列不存在

后来改成这样也是


alter table _Crm_Company Add [CustomCode] nvarchar(50) not null default ''

while( 1 = 1 )
begin

WAITFOR DELAY '00:00:15'

if exists( select [name] from syscolumns where id=object_id('_Crm_Company') and [name] = 'CustomCode' )
begin
update _Crm_Company set CustomCode = EnglishName
break
end
end

[47522341]

楼上所述代码可以执行通过；没有问题；

if object_id(N'crm_company') is not null
drop table crm_company
go

create table crm_company(f1 int)
go
insert into crm_company values(1)
go

if not exists( select [name] from syscolumns where id=object_id('Crm_Company') and [name] = 'CustomCode' )
begin
alter table Crm_Company Add [CustomCode] nvarchar(50) not null default ''

update Crm_Company set CustomCode = 'df'
end

[da21]

你的表有数据吗？如果该表有数据，那么加一个not null 列是不行的。

[baoshan]

--try
if not exists( select [name] from syscolumns where id=object_id('_Crm_Company') and [name] = 'CustomCode' )
alter table _Crm_Company Add [CustomCode] nvarchar(50) not null default ''
go
update _Crm_Company set CustomCode = EnglishName

[ahui_net]

没有 null数据
47522341(睡到8：30) 我这里执行不对啊
－－－－－－－－－－－－－－－－－－－－－－－－
忘了说明我的是sql 2005了

[ahui_net]

47522341(睡到8：30) 我在sql 2000 也测试过了，你的代码运行不能通过

[ahui_net]

baoshan(石头) 的加go会报语法错

[zorou_fatal]

USE [Duwamish7]
GO
/****** 对象: Table [dbo].[Authors] 脚本日期: 01/19/2007 13:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter table [dbo].[Authors] Add [CustomCode] nvarchar(50) not null default ''
GO
update [dbo].[Authors] set [CustomCode] = [Name]

这样可以。
我是SQL SERVER 2005 我用的DUWAMISH7的库测试的

[zorou_fatal]

如果你要判断的话
USE [Duwamish7]
GO
/****** 对象: Table [dbo].[Authors] 脚本日期: 01/19/2007 13:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if not exists( select [name] from syscolumns where id=object_id('Authors') and [name] = 'CustomCode' )
begin
alter table [dbo].[Authors] Add [CustomCode] nvarchar(50) not null default ''
end
GO
update [dbo].[Authors] set [CustomCode] = [Name]

[ahui_net]

只有再 if not exists( select [name] from syscolumns where id=object_id('Authors') and [name] = 'CustomCode' ) 条件成立才运行
update [dbo].[Authors] set [CustomCode] = [Name]

[ahui_net]

因为这个sql会重复运行，不能每次都updata

[baoshan]

--try
alter table _Crm_Company Add [CustomCode] nvarchar(50) not null default ''
go
update _Crm_Company set CustomCode = EnglishName

[ahui_net]

直接写是没问题的，但是我是这样的就不行，加go也不行
if not exists( select [name] from syscolumns where id=object_id('_Crm_Company') and [name] = 'CustomCode' )
begin
alter table _Crm_Company Add [CustomCode] nvarchar(50) not null default ''
update _Crm_Company set CustomCode = EnglishName
end

[hyc_music1981]

确认EnglishName列存在？

[marco08]

EnglishName列是否存在為NULL的數據?

[ahui_net]

顶

[ahui_net]

再顶,顶顶顶

[yjlhch]

关注

[ahui_net]

继续顶

[ahui_net]

高手呢，还没有人回复？！