Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OUTPUT/UPDATE - log data from the updated table

Status
Not open for further replies.

NiceArms

Programmer
May 21, 2009
105
GB
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.

Code:
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!
 
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

 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top