| Author |
OUTPUT Clause SQL Server 2005
|
|
| Adarsh 2006-02-28, 8:23 pm |
| 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
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.googlegroups.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
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| ??
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| ??
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.googlegroups.com...
> ??
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| Yes this works but my update query is having WHERE Clause...
Thanks Uri.
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
>
> Thanks Uri.
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| 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
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.googlegroups.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
>
| |
| Tibor Karaszi 2006-02-28, 8:23 pm |
| 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.googlegroups.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
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| 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.
| |
| Dan Guzman 2006-02-28, 8:23 pm |
| Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@gmail.com> wrote in message
news:1141132215.382713.261120@e56g2000cwe.googlegroups.com...
> 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.
>
| |
| Tibor Karaszi 2006-02-28, 8:23 pm |
| The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
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:1141132215.382713.261120@e56g2000cwe.googlegroups.com...
> 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.
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| #$%&*(...... Oh ok..... I got it now..;)
Thanks a lot Dan, Tibor and Uri....
|
|
|
|