Back devices: clause

Showing posts with label clause. Show all posts

Friday, March 30, 2012

How can I remove a duplicate row?

I have a table that has two rows in it that are identical and need to
delete one of them. Since any kind of where clause in a delete
statement will hit both of them, how can I remove one of these rows?
TIAPick the dups by grouping and put them in a temp table. remove all
duplicate records from the parent table and insert the groped
duplicates (in this table, you should have one occourance of each
record) from the temp table into this old table that dosent have any
rows of these duplicate records.
Hope I didnt loose you with my wording.. ..! (It would help if you had
given some DDL code...!|||http://www.aspfaq.com/2431
Now how about having a key and/or other constraints to prevent this from
happening again?
http://www.aspfaq.com/2509
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com...
> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA|||On 24 Jan 2005 11:08:44 -0800, "QueryBuilder" <pg.242w@.gmail.com>
wrote:
>Pick the dups by grouping and put them in a temp table. remove all
>duplicate records from the parent table and insert the groped
>duplicates (in this table, you should have one occourance of each
>record) from the temp table into this old table that dosent have any
>rows of these duplicate records.
>Hope I didnt loose you with my wording.. ..! (It would help if you had
>given some DDL code...!
This makes sense. I was hoping I could remove the extra in place. (My
thinking is that if the DB knows there are two rows there must be
someway that it uniquely identifies them.)|||Matthew Speed <mspeed@.mspeed.net> wrote in
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com:
> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA
set rowcount 1
delete from t1
where col1=1 and col2=1|||On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>http://www.aspfaq.com/2431
>Now how about having a key and/or other constraints to prevent this from
>happening again?
The keys are now in place. This was a client database I was hired to
do some work on. When I saw the problem my first thought was to add
constraints but one can't implement a unique constraint unless the
existing data is already unique.
>http://www.aspfaq.com/2509|||"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:cjmav0dg4s0uki574285s1rekljfod4lfa@.4ax.com...
> On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
> <ten.xoc@.dnartreb.noraa> wrote:
> >http://www.aspfaq.com/2431
> >
> >Now how about having a key and/or other constraints to prevent this
from
> >happening again?
> The keys are now in place. This was a client database I was hired
to
> do some work on. When I saw the problem my first thought was to add
> constraints but one can't implement a unique constraint unless the
> existing data is already unique.
I add identitiy columns to tables all the time to get rid of dups. You
can leave the identitiy column around or simply remove it after you
are done. Another technique is to create a new table as select
distinct * from xxx, drop the old one and rename the new one as
appropriately.|||> I add identitiy columns to tables all the time to get rid of dups.
With sensible design, you won't have dupes at all. Does your data not have
a candidate key? Do you not use primary keys for any reason?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVkkXtmAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> > I add identitiy columns to tables all the time to get rid of dups.
> With sensible design, you won't have dupes at all. Does your data
not have
> a candidate key? Do you not use primary keys for any reason?
Data is often loaded in a database before being transformed and
duplicates removed. This is what happens in the real world.|||> Data is often loaded in a database before being transformed and
> duplicates removed. This is what happens in the real world.
The real world? What is that? Glad my job is inside a vacuum, where we
load data into a staging table, insert the NEW, NON-DUPLICATE rows into our
production tables, then blow away or archive the staging data. (Rather than
just throwing everything into our production tables, and removing duplicates
there.)|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uHq19RnAFHA.2572@.TK2MSFTNGP10.phx.gbl...
> > Data is often loaded in a database before being transformed and
> > duplicates removed. This is what happens in the real world.
> The real world? What is that? Glad my job is inside a vacuum,
where we
> load data into a staging table
Now, think about your "staging" table and the tables I am talking
about. Is the light coming on yet?|||> Now, think about your "staging" table and the tables I am talking
> about. Is the light coming on yet?
Funny that you removed the rest of my description, which kind of usurps your
petty insult. Grow up.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eBY9JUoAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> > Now, think about your "staging" table and the tables I am talking
> > about. Is the light coming on yet?
> Funny that you removed the rest of my description, which kind of
usurps your
> petty insult. Grow up.
Sorry if I struck a nerve. I only responded to your post in the same
"tone" that was directed at me. Concerning the part of your post that
was redacted it was simply irrelevant. If you still don't get it I'm
not sure I can dumb it down any further. Sorry :(
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||> was redacted it was simply irrelevant. If you still don't get it I'm
> not sure I can dumb it down any further. Sorry :(
Oh christ. You know what everybody? I am sick and tired of helping people
to end up dealing with this childish crap. I have better things to do with
my time than to be at the wrong end of someone's childish recess attacks.
See ya.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebL2Z7oAFHA.1400@.TK2MSFTNGP11.phx.gbl...
> > was redacted it was simply irrelevant. If you still don't get it
I'm
> > not sure I can dumb it down any further. Sorry :(
> Oh christ. You know what everybody? I am sick and tired of helping
people
> to end up dealing with this childish crap. I have better things to
do with
> my time than to be at the wrong end of someone's childish recess
attacks.
> See ya.
You're actually trying to help?
Here's some helpful advice.
If you're going to post with this attitude of condescension, at least
get your facts right. You might consider thinking a bit more about the
questions and replys before posting. You also seem to enjoy putting
people on the defensive and when you can't get your way you end up
lashing out like a child.
I'll let you get you the last word in since you seem intent on
"helping" me until I give up.|||/*select duplicate rows
select count(trnno), trnno from dw_ndls_train
group by trnno
having count(trnno) > 1
/*del duplicate rows
delete from dw_ndls_train
where (trnno, trname)
not in
( select min(trnno), trname
from dw_ndls_train group by trname
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How can I remove a duplicate row?

Friday, March 23, 2012

How can I pass a parameter to use in an IN

I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.

Dim MySqlParamSelected As New SqlParameter("@.Selected", SqlDbType.Int)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("intSelected")

WHERE tblSelected.Selected_ID IN (@.Selected)

It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.

Here is the error:

Msg 119, Level 15, State 1, Line 4
Must pass parameter number 7 and subsequent parameters as'@.name = value'. After the form'@.name = value' has been used, all subsequent parameters must be passed in the form'@.name = value'.

Does anyone know how I can correctly pass multiple numbers to use in my IN?

Have a look atthis article which discusses this very issue.

|||

Oh, that is a very clever little trick!

|||

I have tried using fn_split function from the artical.

I now get the following error:

Failed to convert parameter value from a String to a Int32.

Does anyone have any Ideas about this?

|||

Did you run the debugger to find out which parameter it was complaining about? What value you were passing in? Whether it should be an Int32 parameter instead of a string parameter?

|||

Do you mean placing break points? I'm not sure how to check for all of the things you listed.

My code_ID data type is int, my parameter is and int, but I'm quessing my session variable is a string. If I'm passing to the parameter '78,79' I don't see how it will ever be and int. This thing has me very confused.

I appreciate any help with this I can get!!!

|||

Your new procedure should take a varchar parameter (string) of id values i.e. '1,2,3,4,5' and that function will split it up. You should'nt be sending an int parameter.

|||

I must be missing something, if I change my parameter to a varchar I get this: Error converting data type varchar to int.

Dim MySqlParamSelectedAs New SqlParameter("@.Selected", SqlDbType.VarChar)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("Selected")

I checked the session varaiable and it is '78,79'

Here is what is in my where

tblTemplates.Template_IDIN(SELECTValueFROM fn_Split(@.Selected,','))

Template_ID is an Int datatype.

What could I be doing wrong?

|||

Ok, I got it. It was my tunnel vision, I was so hung up on looking at the WHERE I didn't see that my @.Selected parameter we declared as an Int.

|||

Just FYI: if you have a few values it might work out okay but if you have hundreds of values it could be a little slow. the IN is internally converted to OR and SQL Server will look for each value in the IN. If you do a JOIN it might be a little faster.

|||

ndinakar,

I must ask, what do you mean by doing a Join?

|||

Friday, February 24, 2012

How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.

How Can I get my sproc to return records with 0 as ClientID

Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?

If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).

If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = Isnull(@.SID,C.ClientID)

u can modify the where close like this

ALTER PROCEDURE dbo.CMAdmin

@.SID int

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = COALESCE(@.SID,C.ClientID)

this query will return all records when client id is null and when it is not null it only returns rows specified in the where condition

for more details

http://www.sqlteam.com/item.asp?ItemID=2077

regards

Sunday, February 19, 2012

How can I find out who is running dangerous queries

I recently discovered that some one (or application) updated all the
rows in the database. Looks like a query was missing a where clause.
It was probably one of the applications, but reviews of the code show
that a where clauses is always used, or so it appears. Anyway, I set
up a trigger to capture every update to a particular table that
recorded who did what and when. I created the trigger (on insert and
update) on a table and in it I use the new fn_get_sql function that
comes with SQL Server 2000 SP3. It looks like this:

----------
CREATE TRIGGER Update_Last_Modified ON [dbo].[MYTABLENAME]
FOR UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON
DBCC TRACEON (2861)

DECLARE @.Qry nvarchar(4000)

DECLARE @.handle binary(20)

SELECT @.handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.SPID

SET @.QRY = (SELECT CONVERT(nvarchar(4000), [text]) FROM
::fn_get_sql(@.handle))

UPDATE MYTABLENAME
SET DATE_LAST_MODIFIED = GETDATE(),
LAST_COMMAND = @.QRY,
LAST_USER = SYSTEM_USER
FROM inserted
WHERE MYTABLENAME.UID= Inserted.UID
END

-----------

It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
but I was limited to the first 255 characters of the command, which
prevented me from seeing the critical parts, like the where clause!
When I modified the trigger to use fn_get_sql, all I ever see is the
entire text of the create trigger command. Maybe I should use an
entirely different approach. I'm open to ideas.

Thanks very much in advance for your help!

Miles

_________________Miles (milesfeinberg@.hotmail.com) writes:
> It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
> but I was limited to the first 255 characters of the command, which
> prevented me from seeing the critical parts, like the where clause!
> When I modified the trigger to use fn_get_sql, all I ever see is the
> entire text of the create trigger command. Maybe I should use an
> entirely different approach. I'm open to ideas.

Yes, the idea with fn_get_sql is to get the currently executing statement
of a procedure. And for a process that introspects itself, the current
statement will be the statement it queries sysprocesses. So in your
case DBCC INPUTBUFFER is a better bet.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 30, 2012

How can I remove a duplicate row?

How can I remove a duplicate row?

How can I remove a duplicate row?

Friday, March 23, 2012

How can I pass a parameter to use in an IN

Friday, February 24, 2012

How can i get the 2nd, 3rd or N''th row from a table?

How can i get the 2nd, 3rd or N''th row from a table?

How can i get the 2nd, 3rd or N''th row from a table?

How Can I get my sproc to return records with 0 as ClientID

Sunday, February 19, 2012

How can I find out who is running dangerous queries

Back devices

Blog Archive

About Me