OUTPUT/UPDATE - log data from the updated table

[NiceArms]

Hi,

the follwing code is trying to insert into table1 the deleted data and some static data following an update of table2. I am trying to get the field AccountID from table1 to update into table2 but i keep receiving the following...
The multi-part identifier "table1.AccountID" could not be bound.

DECLARE @Field AS NVARCHAR(20)
SET @Field = 'homeno1'

UPDATE table1 
SET @Field = NULL
	OUTPUT	table1.accountID, -- issue line
			@Field,
			deleted.homeno1,
			'nicearms'
	INTO table1
		(bcwref, numbertype, numbervalue, username)
FROM table2 INNER JOIN 
	 table1 ON phoneNum = @Field

any thoughts?

Nicearms

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[simian336]

Don't think you can do....

UPDATE table1
SET @Field = NULL

without making the whole statement dynamic.

Untested, but something like...

Declare @sql varchar(500)

set @sql ='UPDATE table1 SET ' + @Field '= NULL '
set @sql = @sql + 'OUTPUT table1.accountID, ' + @Field
set @sql = @sql + ', deleted.homeno1, ''nicearms'''
set @sql = @sql + 'INTO table1 (bcwref, numbertype, numbervalue, username)'
set @sql = @sql + 'FROM table2 INNER JOIN table1 ON phoneNum = ' + @Field

Exec @sql

Simi

 

[NiceArms]

I have found another way of doing it but I will still try this.

The original query was going to become dynamic.

Thank you,

nice

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!