OUTPUT Clause SQL Server 2005

Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT query
Hi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||?
|||?
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegr oups.com...
> ?
>
|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.
|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegr oups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>
|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query
|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegr oups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>
|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.