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.
Showing posts with label clause. Show all posts
Showing posts with label clause. Show all posts
Friday, March 30, 2012
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 queryHi
> 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
>|||'|||'
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...
> '
>|||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.googlegroups.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.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
>|||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
>|||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, 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.
>|||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.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
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 queryHi
> 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
>|||'|||'
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...
> '
>|||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.googlegroups.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.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
>|||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
>|||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, 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.
>|||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.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
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 queryHi
> 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
>|||'|||'
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...
> '
>|||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.googlegroups.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.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
>|||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
>|||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.
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 queryHi
> 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
>|||'|||'
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...
> '
>|||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.googlegroups.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.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
>|||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
>|||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.
OUTPUT Clause
We have a table, which is partitioned on a computed column, based on a UDF function (custom logic). We populate this computed column in an INSTEAD OF Trigger. But when we try to "output" the computed column using OUTPUT caluse, NULL values are returned for computed column. SQL BOL is not clear on this part of using output clause in instead of triggers. This does not even work for a table that is not partitioned. Here is an example of what we are trying to do...
I would appreciate any help on this...
IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test
GO
CREATE TABLE dbo.Test
( IdCol INT IDENTITY(1,1) NOT NULL,
Date DATETIME NOT NULL,
CalcCol CHAR(10) NOT NULL
)
GO
CREATE TRIGGER trgTest_InsUpd ON dbo.Test
INSTEAD OF INSERT AS
BEGIN
INSERT INTO dbo.Test
( Date, CalcCol ) SELECT Date, CONVERT(CHAR(10), Date, 101) FROM INSERTED
END
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp(IdCol INT, Date DATETIME, CalcCol VARCHAR(10))
TRUNCATE TABLE #tmp
INSERT INTO dbo.Test(Date) OUTPUT INSERTED.IdCol, INSERTED.Date, INSERTED.CalcCol INTO #tmp
VALUES(GETDATE())
--Here the identity and Computed column are returned as NULL
SELECT * FROM #tmp
SELECT * FROM dbo.Test
Instead of trigger would not have data in INSERTED and DELETED tables, because instead of the original insert statement your INSTEAD OF tRIGGER is getting fired. The tables will be populated if you write a AFTER TRIGGER.Monday, March 26, 2012
outer join in where clause
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):
select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'
Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.
Regards,
hmscott
Originally posted by Lambik
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as
Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!
The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.
Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'
I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):
select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'
Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.
Regards,
hmscott
Originally posted by Lambik
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as
Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!
The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.
Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'
I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.
Friday, March 23, 2012
Outer join bug
In a SELECT query with a left join, I have an ON clause
with two conditions -- one compares a column from each
table and the other compares a column in the left table to
a constant value. The results should be equivalent to
putting the second comparison (to a constant) in a WHERE
clause and in the execution plan, the second comparison
appears in the WHERE condition of the join. The results
include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=table2.A AND table1.B=1
table1
Row 1: A=1, B=1
Row 2: A=1, B=2
table2
Row 1: A=1, B=1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was
also not joined with the row in table2.
(Is this the correct newsgroup for this question?)This is a multi-part message in MIME format.
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This is not a bug - it's a trap. If you subscribe to SQL Server =Professional, you can check out my article on this at:
http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503632AF9A852568E2=
007AD8BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John VMT" <jbeckett@.vmtsoftware.com> wrote in message =news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
In a SELECT query with a left join, I have an ON clause with two conditions -- one compares a column from each table and the other compares a column in the left table to a constant value. The results should be equivalent to putting the second comparison (to a constant) in a WHERE clause and in the execution plan, the second comparison appears in the WHERE condition of the join. The results include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=3Dtable2.A AND table1.B=3D1
table1
Row 1: A=3D1, B=3D1
Row 2: A=3D1, B=3D2
table2
Row 1: A=3D1, B=3D1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was also not joined with the row in table2.
(Is this the correct newsgroup for this question?)
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This is not a bug - it's a trap. =If you subscribe to SQL Server Professional, you can check out my article on =this at:
http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/0F792E503632AF9A852568E2007AD8BC?open&login=-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John VMT"= wrote in message news:0de601c35b6e$62=2b4940$a601280a@.phx.gbl...In a SELECT query with a left join, I have an ON clause with two =conditions -- one compares a column from each table and the other compares a =column in the left table to a constant value. The results should be =equivalent to putting the second comparison (to a constant) in a WHERE clause =and in the execution plan, the second comparison appears in the WHERE =condition of the join. The results include rows that should have been =filtered out.Example:select table1.A, table1.B, table2.Afrom =table1 left outer join table2on table1.A=3Dtable2.A AND table1.B=3D1table1Row 1: A=3D1, B=3D1Row 2: A=3D1, B=3D2table2Row 1: A=3D1, B=3D1resultsRow 1: 1, =1, 1Row 2: 1, 2, The second row of the results should not be there. It was also not joined with the row in =table2.(Is this the correct newsgroup for this question?)
--=_NextPart_000_0100_01C35B4D.9E3AD110--|||Thanks for the quick feedback and correction. It's a bad
habit I got into several years ago with Jet databases (it
improved query performance).
It was unfortunate that the execution plan showed the
condition (column=<constant>) in the join's WHERE clause.
So the plan was as I expected but the execution was not.
Life is a series of disappointments when you hope for what
you want instead of what you get.
>--Original Message--
>This is not a bug - it's a trap. If you subscribe to SQL
Server Professional, you can check out my article on this
at:
>http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503
632AF9A852568E2007AD8BC?open&login
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"John VMT" <jbeckett@.vmtsoftware.com> wrote in message
news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
>In a SELECT query with a left join, I have an ON clause
>with two conditions -- one compares a column from each
>table and the other compares a column in the left table
to
>a constant value. The results should be equivalent to
>putting the second comparison (to a constant) in a WHERE
>clause and in the execution plan, the second comparison
>appears in the WHERE condition of the join. The results
>include rows that should have been filtered out.
>Example:
>select table1.A, table1.B, table2.A
>from table1 left outer join table2
>on table1.A=table2.A AND table1.B=1
>table1
>Row 1: A=1, B=1
>Row 2: A=1, B=2
>table2
>Row 1: A=1, B=1
>results
>Row 1: 1, 1, 1
>Row 2: 1, 2, <null>
>The second row of the results should not be there. It
was
>also not joined with the row in table2.
>(Is this the correct newsgroup for this question?)
>
with two conditions -- one compares a column from each
table and the other compares a column in the left table to
a constant value. The results should be equivalent to
putting the second comparison (to a constant) in a WHERE
clause and in the execution plan, the second comparison
appears in the WHERE condition of the join. The results
include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=table2.A AND table1.B=1
table1
Row 1: A=1, B=1
Row 2: A=1, B=2
table2
Row 1: A=1, B=1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was
also not joined with the row in table2.
(Is this the correct newsgroup for this question?)This is a multi-part message in MIME format.
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This is not a bug - it's a trap. If you subscribe to SQL Server =Professional, you can check out my article on this at:
http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503632AF9A852568E2=
007AD8BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John VMT" <jbeckett@.vmtsoftware.com> wrote in message =news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
In a SELECT query with a left join, I have an ON clause with two conditions -- one compares a column from each table and the other compares a column in the left table to a constant value. The results should be equivalent to putting the second comparison (to a constant) in a WHERE clause and in the execution plan, the second comparison appears in the WHERE condition of the join. The results include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=3Dtable2.A AND table1.B=3D1
table1
Row 1: A=3D1, B=3D1
Row 2: A=3D1, B=3D2
table2
Row 1: A=3D1, B=3D1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was also not joined with the row in table2.
(Is this the correct newsgroup for this question?)
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This is not a bug - it's a trap. =If you subscribe to SQL Server Professional, you can check out my article on =this at:
http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/0F792E503632AF9A852568E2007AD8BC?open&login=-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John VMT"
--=_NextPart_000_0100_01C35B4D.9E3AD110--|||Thanks for the quick feedback and correction. It's a bad
habit I got into several years ago with Jet databases (it
improved query performance).
It was unfortunate that the execution plan showed the
condition (column=<constant>) in the join's WHERE clause.
So the plan was as I expected but the execution was not.
Life is a series of disappointments when you hope for what
you want instead of what you get.
>--Original Message--
>This is not a bug - it's a trap. If you subscribe to SQL
Server Professional, you can check out my article on this
at:
>http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503
632AF9A852568E2007AD8BC?open&login
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"John VMT" <jbeckett@.vmtsoftware.com> wrote in message
news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
>In a SELECT query with a left join, I have an ON clause
>with two conditions -- one compares a column from each
>table and the other compares a column in the left table
to
>a constant value. The results should be equivalent to
>putting the second comparison (to a constant) in a WHERE
>clause and in the execution plan, the second comparison
>appears in the WHERE condition of the join. The results
>include rows that should have been filtered out.
>Example:
>select table1.A, table1.B, table2.A
>from table1 left outer join table2
>on table1.A=table2.A AND table1.B=1
>table1
>Row 1: A=1, B=1
>Row 2: A=1, B=2
>table2
>Row 1: A=1, B=1
>results
>Row 1: 1, 1, 1
>Row 2: 1, 2, <null>
>The second row of the results should not be there. It
was
>also not joined with the row in table2.
>(Is this the correct newsgroup for this question?)
>
Outer Join and WHERE clause strange behavior
I am writing a stored procedure to get aggregrate data out of a web site log
table by joining it with a view
DDL for the table:
CREATE TABLE w3cexlog (
pacifictime datetime,
[cs-method] varchar(20),
target varchar(8000),
query varchar(2000),
username varchar(100),
browser varchar(8000),
referrer varchar(2000),
status int,
substatus int,
win32status int,
[sc-bytes] bigint,
[cs-bytes] bigint,
[time-taken] bigint
)
The view is through ADSI, and pulls the Active Directory OU, samAccountName
(which corresponds to username), and full name for the user.
If the view where a base table, it's DDL would look like this
CREATE TABLE PortalUsers (
name nvarchar(512),
samAccountName nvarchar(512),
OU nvarchar(8000)
)
I want to find out how many hits where generated by users in each OU, along
with visitors from each OU, by joining PortalUsers to w3cexlog on
PortalUsers.samAccountName = w3cexlog.username. (The web site uses AD
authentication, so they must match).
Here is the query:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
ORDER BY 2 DESC, 1
This query gives the expected results; OUs for which none of its users
produced a "hit" still show in the output, but with zero's in the "hits" and
"visitors" columns. However, if I add a WHERE clause to query a specific dat
e
range:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
ORDER BY 2 DESC, 1
The OUs that produces no hits are now gone from the result set! That WHERE
clause wouldn't have removed any rows for the OUs that didn't have hits; the
y
where never there to begin with; so why does the behavior of the outer join
change?
In order to get around this, I constructed the following query
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
) t2
ON t1.OU = t2.OU
ORDER BY 2 DESC, 1
But the performance of this is terrible."Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
>I am writing a stored procedure to get aggregrate data out of a web site
>log
> table by joining it with a view
>
> SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
> visitors
> FROM portalusers p LEFT JOIN w3cexlog w on
> p.samaccountname=w.username
> WERE w.pacifictime BETWEEN '20060102' AND '20060112'
> GROUP BY p.ou
> ORDER BY 2 DESC, 1
> The OUs that produces no hits are now gone from the result set! That WHERE
> clause wouldn't have removed any rows for the OUs that didn't have hits;
> they
> where never there to begin with; so why does the behavior of the outer
> join
> change?
>
Mark,
This is the way things work. An outer join may contain results
that fail the ON condition, but it will never contain results that fail
the WHERE condition. The WHERE condition is a real filter
than can be thought of as being applied to the result of the
outer join.
If you have result rows with 0 hits, all the result columns from
w3cexlog in those result rows contain NULL. NULL will never
be between '20060102' and '20060112', so you'll see no rows
that failed the ON condition.
Maybe what you want is just
... on p.samaccountname=w.username
AND w.pacifictime BETWEEN '20060102' AND '20060112'
...
Steve Kass
Drew University|||Another option is to leave the join unchanged and alter the where caluse lik
e
this:
...
WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
null)
...
ML
http://milambda.blogspot.com/|||Thanks Steve and ML. I should have seen it, but I was a little tired! I had
the incorrect thinking that the WHERE clause was applied before the join,
instead of after.
"Steve Kass" wrote:
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
> Mark,
> This is the way things work. An outer join may contain results
> that fail the ON condition, but it will never contain results that fail
> the WHERE condition. The WHERE condition is a real filter
> than can be thought of as being applied to the result of the
> outer join.
> If you have result rows with 0 hits, all the result columns from
> w3cexlog in those result rows contain NULL. NULL will never
> be between '20060102' and '20060112', so you'll see no rows
> that failed the ON condition.
> Maybe what you want is just
> ... on p.samaccountname=w.username
> AND w.pacifictime BETWEEN '20060102' AND '20060112'
> ...
> Steve Kass
> Drew University
>
>|||It will work, but I wouldn't suggest this as a good option, I'm afraid.
This is like adding "and I really meant that is was an outer join" to the
where clause.
Or did you see some advantage with putting it in the where clause, like some
problem that doing it this way might solve?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F48CD19-D89E-4535-8FFD-0045DF823CA1@.microsoft.com...
> Another option is to leave the join unchanged and alter the where caluse
> like
> this:
> ...
> WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
> null)
> ...
>
> ML
> --
> http://milambda.blogspot.com/|||Another option is to move the condition to the ON clause:
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
visitors, pacifictime
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
) t2
ON (t1.OU = t2.OU )
AND t2.pacifictime BETWEEN '20060102' AND '20060112'
ORDER BY 2 DESC, 1
That might perform better|||On Sat, 14 Jan 2006 04:46:01 -0800, ML wrote:
>Another option is to leave the join unchanged and alter the where caluse li
ke
>this:
>...
>WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
>null)
>...
Hi ML,
That would also include rows with w.pacifictime equal to NULL in the
base table (assuming the column is nullable); I don't think that this is
what Mark wants.
Hugo Kornelis, SQL Server MVP|||Since both methods yield same results the only argument would be performance
.
So, which one would perform better?
ML
http://milambda.blogspot.com/|||On Sun, 15 Jan 2006 14:09:01 -0800, ML wrote:
>Since both methods yield same results the only argument would be performanc
e.
>So, which one would perform better?
Hi ML,
Have you testeed them?
(I'll gladly admit I didn't - but due to the OR in your method, I'd be
surprised if it turns out to be the faster version).
Of course, they don't yield the same results if the column is nullible.
Hugo Kornelis, SQL Server MVP|||Beyond what was discussed about how if the pacifictime column was nullable
(you would want to check for the join columns being null) the argument is
style and symantics.
I would argue that it is very important to write readable queries that can
be understood by the next reader AND that make sense. Performance is
important, and if the less clear version peformed measurably better (if
anything it would likely be no better than equal, but sometimes there can be
reasons to do things that don't seem kosher for performance sake, but this
is unlikely to be one of those cases.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:61118E3B-FAED-4124-86F5-26219564531A@.microsoft.com...
> Since both methods yield same results the only argument would be
> performance.
> So, which one would perform better?
>
> ML
> --
> http://milambda.blogspot.com/
table by joining it with a view
DDL for the table:
CREATE TABLE w3cexlog (
pacifictime datetime,
[cs-method] varchar(20),
target varchar(8000),
query varchar(2000),
username varchar(100),
browser varchar(8000),
referrer varchar(2000),
status int,
substatus int,
win32status int,
[sc-bytes] bigint,
[cs-bytes] bigint,
[time-taken] bigint
)
The view is through ADSI, and pulls the Active Directory OU, samAccountName
(which corresponds to username), and full name for the user.
If the view where a base table, it's DDL would look like this
CREATE TABLE PortalUsers (
name nvarchar(512),
samAccountName nvarchar(512),
OU nvarchar(8000)
)
I want to find out how many hits where generated by users in each OU, along
with visitors from each OU, by joining PortalUsers to w3cexlog on
PortalUsers.samAccountName = w3cexlog.username. (The web site uses AD
authentication, so they must match).
Here is the query:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
ORDER BY 2 DESC, 1
This query gives the expected results; OUs for which none of its users
produced a "hit" still show in the output, but with zero's in the "hits" and
"visitors" columns. However, if I add a WHERE clause to query a specific dat
e
range:
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
ORDER BY 2 DESC, 1
The OUs that produces no hits are now gone from the result set! That WHERE
clause wouldn't have removed any rows for the OUs that didn't have hits; the
y
where never there to begin with; so why does the behavior of the outer join
change?
In order to get around this, I constructed the following query
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS visito
rs
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
WERE w.pacifictime BETWEEN '20060102' AND '20060112'
GROUP BY p.ou
) t2
ON t1.OU = t2.OU
ORDER BY 2 DESC, 1
But the performance of this is terrible."Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
>I am writing a stored procedure to get aggregrate data out of a web site
>log
> table by joining it with a view
>
> SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
> visitors
> FROM portalusers p LEFT JOIN w3cexlog w on
> p.samaccountname=w.username
> WERE w.pacifictime BETWEEN '20060102' AND '20060112'
> GROUP BY p.ou
> ORDER BY 2 DESC, 1
> The OUs that produces no hits are now gone from the result set! That WHERE
> clause wouldn't have removed any rows for the OUs that didn't have hits;
> they
> where never there to begin with; so why does the behavior of the outer
> join
> change?
>
Mark,
This is the way things work. An outer join may contain results
that fail the ON condition, but it will never contain results that fail
the WHERE condition. The WHERE condition is a real filter
than can be thought of as being applied to the result of the
outer join.
If you have result rows with 0 hits, all the result columns from
w3cexlog in those result rows contain NULL. NULL will never
be between '20060102' and '20060112', so you'll see no rows
that failed the ON condition.
Maybe what you want is just
... on p.samaccountname=w.username
AND w.pacifictime BETWEEN '20060102' AND '20060112'
...
Steve Kass
Drew University|||Another option is to leave the join unchanged and alter the where caluse lik
e
this:
...
WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
null)
...
ML
http://milambda.blogspot.com/|||Thanks Steve and ML. I should have seen it, but I was a little tired! I had
the incorrect thinking that the WHERE clause was applied before the join,
instead of after.
"Steve Kass" wrote:
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:5002E6BF-4C4F-48B1-B333-D7B398C42430@.microsoft.com...
> Mark,
> This is the way things work. An outer join may contain results
> that fail the ON condition, but it will never contain results that fail
> the WHERE condition. The WHERE condition is a real filter
> than can be thought of as being applied to the result of the
> outer join.
> If you have result rows with 0 hits, all the result columns from
> w3cexlog in those result rows contain NULL. NULL will never
> be between '20060102' and '20060112', so you'll see no rows
> that failed the ON condition.
> Maybe what you want is just
> ... on p.samaccountname=w.username
> AND w.pacifictime BETWEEN '20060102' AND '20060112'
> ...
> Steve Kass
> Drew University
>
>|||It will work, but I wouldn't suggest this as a good option, I'm afraid.
This is like adding "and I really meant that is was an outer join" to the
where clause.
Or did you see some advantage with putting it in the where clause, like some
problem that doing it this way might solve?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F48CD19-D89E-4535-8FFD-0045DF823CA1@.microsoft.com...
> Another option is to leave the join unchanged and alter the where caluse
> like
> this:
> ...
> WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
> null)
> ...
>
> ML
> --
> http://milambda.blogspot.com/|||Another option is to move the condition to the ON clause:
SELECT t1.OU, t2.hits, t2.visitors FROM
(
SELECT DISTINCT OU FROM PortalUsers
) t1
LEFT JOIN
(
SELECT p.ou, count(w.username) AS hits, count(distinct w.username) AS
visitors, pacifictime
FROM portalusers p LEFT JOIN w3cexlog w on
p.samaccountname=w.username
GROUP BY p.ou
) t2
ON (t1.OU = t2.OU )
AND t2.pacifictime BETWEEN '20060102' AND '20060112'
ORDER BY 2 DESC, 1
That might perform better|||On Sat, 14 Jan 2006 04:46:01 -0800, ML wrote:
>Another option is to leave the join unchanged and alter the where caluse li
ke
>this:
>...
>WHERE (w.pacifictime BETWEEN '20060102' AND '20060112' or w.pacifictime is
>null)
>...
Hi ML,
That would also include rows with w.pacifictime equal to NULL in the
base table (assuming the column is nullable); I don't think that this is
what Mark wants.
Hugo Kornelis, SQL Server MVP|||Since both methods yield same results the only argument would be performance
.
So, which one would perform better?
ML
http://milambda.blogspot.com/|||On Sun, 15 Jan 2006 14:09:01 -0800, ML wrote:
>Since both methods yield same results the only argument would be performanc
e.
>So, which one would perform better?
Hi ML,
Have you testeed them?
(I'll gladly admit I didn't - but due to the OR in your method, I'd be
surprised if it turns out to be the faster version).
Of course, they don't yield the same results if the column is nullible.
Hugo Kornelis, SQL Server MVP|||Beyond what was discussed about how if the pacifictime column was nullable
(you would want to check for the join columns being null) the argument is
style and symantics.
I would argue that it is very important to write readable queries that can
be understood by the next reader AND that make sense. Performance is
important, and if the less clear version peformed measurably better (if
anything it would likely be no better than equal, but sometimes there can be
reasons to do things that don't seem kosher for performance sake, but this
is unlikely to be one of those cases.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:61118E3B-FAED-4124-86F5-26219564531A@.microsoft.com...
> Since both methods yield same results the only argument would be
> performance.
> So, which one would perform better?
>
> ML
> --
> http://milambda.blogspot.com/
Subscribe to:
Posts (Atom)