Deleting Records in a Table

Hi,
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten|||Originally posted by CarstenK
Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten|||Originally posted by CarstenK
Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten

Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,

the normal syntax for delete looks like

delete from <table_name>
[where <where_condition>

Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.

Here is my preferred method, using joins instead of where clause:

delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1

WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.

blindman|||Hi, I used the owner moon:

create table moon.tempt
(tid integer primary key,
value integer)

insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)

delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)

finally:

select * from moon.tempt

tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run

select min(t2.tid) from moon.tempt t2 where t2.value = t1.value

...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.

Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.

blindman

Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from

---

SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/

quote:

---

>.
>

---

|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of

---

select..group by

quote:

---

>statement.
> If you want to use group by statement, the content you

---

select should in

quote:

---

>the group by or have a function tell the machine how to

---

modify the data.

quote:

---

>Best Wishes
>Wei Ci Zhou
>
>.
>

---

|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy

quote:

---

>--Original Message--
>Wei,
>What should I do to delete these records' Can you

---

help

quote:

---

>with a better script?
>Thanks.
>Laila
>
>
>select..group by
you[QUOTE]
>select should in
>modify the data.
>.
>

---

|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?

Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
--
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
>.
>|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
>statement.
> If you want to use group by statement, the content you
select should in
>the group by or have a function tell the machine how to
modify the data.
>Best Wishes
>Wei Ci Zhou
>
>.
>|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
>with a better script?
>Thanks.
>Laila
>
>>--Original Message--
>>Hi:
>> I think the error is becasue the misunderstand of
>select..group by
>>statement.
>> If you want to use group by statement, the content
you
>select should in
>>the group by or have a function tell the machine how to
>modify the data.
>>Best Wishes
>>Wei Ci Zhou
>>
>>.
>.
>|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?

Deleting dupes in special cases

I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.

I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];

but it doesn't work since it's keeping the subset of the dupe row(s).

Can someone shed some light?

TIASo why do you keep 2 instead of 1?

EDIT: You won't be able to answer that one..unless you say that because it comes "first"...is that it?|||Because it comes first :)|||OK

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int, contact_id_tmp int, account_id int, emailAddress varchar(255))
GO

INSERT INTO myTable99(temp_id, contact_id_tmp, account_id, emailAddress)
SELECT 1, 1, 3, 'test@.acme.com' UNION ALL
SELECT 2, 1, 5, 'test@.acme.com' UNION ALL
SELECT 3, 1, 10, 'test@.acme.com' UNION ALL
SELECT 4, 2, 12, 'test@.acme.com' UNION ALL
SELECT 5, 2, 3, 'test@.acme.com' UNION ALL
SELECT 6, 2, 9, 'test@.acme.com'
GO

SELECT *
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

You can convert that in to a delete...let me know if you can't|||Thanks very much. Looks like this will do the trick if I ever figure out the delete statement for it =\|||Did you cut and paste the example in to QA and run it?

Also, I would use that as SELECT to see the population BEFORE you unleash the delete...oh and make sure you back up the table BEFORE you do the DELETE|||Yes, I ran it in QA and got the expected results. I need to delete R.contact_id_tmp

Currently trying to figure out the delete statement...|||Come on...that's too easy

DELETE FROM myTable99 WHERE contact_id_tmp IN (
SELECT R.contact_id_tmp
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
)

GO

SELECT * FROM myTable99
GO|||OK. This just got uglier... Turns out accounts will be stored as a comma separated list in one field. See the attachment for an example.

Now, is it possible to de-dupe from a comma separated list all in one field? I know I could create a new table and loop over each account-emailAddress combo, but I'd like to know if it's possible to avoid doing this (since it will be much slower) and de-dupe straight from the source table.

Thanks again|||Read this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Then create a new table where all the values are on 1 row, then use the query in this thread|||I tried the UDF but I'm getting 0 rows back. I've attached the sample dataset (1 row) and the UDF call.|||You really should normalized your data...I feel I'm doing more harm than good...

EDIT: This is pure theft from this Article (http://www.sqlteam.com/item.asp?ItemID=2652) written by A most Valuable Yak (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1176) , Rob Volk (http://weblogs.sqlteam.com/robv/)

USE Northwind
GO

SET NOCOUNT ON
SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int IDENTITY(1,1), contact_id_tmp int, account_id int, emailAddress varchar(255))
CREATE TABLE myTable00(contact_id int, accounts varchar(200), emailAddress varchar(255))
CREATE TABLE Tally(ID int)
GO

INSERT INTO myTable00(contact_id, accounts, emailAddress)
SELECT 1, '1,2,3,10,15', 'test@.acme.com' UNION ALL
SELECT 2, '5,10,20,30', 'test@.acme.com' UNION ALL
SELECT 3, '88,42', 'test@.acme.com' UNION ALL
SELECT 4, '1,2,3', 'test2@.acme.com'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 100
BEGIN
INSERT INTO Tally(ID) SELECT @.x
SELECT @.x = @.x + 1
END
GO

INSERT INTO myTable99(contact_id_tmp, Account_Id, emailAddress)
SELECT contact_id AS contact_id_tmp,
NullIf(SubString(',' + accounts + ',' , ID , CharIndex(',' , ',' + accounts + ',' , ID) - ID) , '') AS Account_Id,
emailAddress
FROM Tally, myTable00
WHERE ID <= Len(',' + accounts + ',') AND SubString(',' + accounts + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + accounts + ',' , ID) - ID > 0 --remove this line to keep NULL rows

SELECT * FROM myTable99
GO

-- Now just add the other SQL

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
DROP TABLE Tally
GO|||This is exactly what I needed, but...users have decided to go back and separate each account in individual rows!!

Thanks a lot for your help. I guess I'll check out sqlteam.com from now on before posting here. :)|||Even if they do...the data is still not normalized if you need an operation like this...anyway, it was a neat exercise...|||This is just 1/3 of the entire process. The data is normalized in its final state.

Deleting and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.

Delete using another tables values

Hi all,
I am trying to perform a delete that I could achieve in Access but
need to do this in sql2000.
I have two tables Warranty and Registrations. I would like to delete
all items in the warranty table where there is a match in
registrations on a common field.
I access the query would be:
DELETE warranty.*
FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
registrations.vins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
SamOn 23 Sep 2004 02:40:24 -0700, SG wrote:

>I access the query would be:
>DELETE warranty.*
>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>registrations.vins;
>But cannot replicate this in SQL server?

Hi Sam,

You're almost there. The Transact-SQL version of this would be

DELETE warranty
FROM warranty
INNER JOIN registration
ON warranty.BBMQCE = registrations.vins

Yes - you only need to drop the .* !!!

However, the above is proprietary code that will not port well to other
databases. If you want portability, use the ANSI-standard delete syntax
instead:

DELETE FROM warranty
WHERE NOT EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

(both queries untested - beware of spelling errors!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks - i was so nearly there!
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hugo Kornelis wrote:

> On 23 Sep 2004 02:40:24 -0700, SG wrote:
>
>>I access the query would be:
>>DELETE warranty.*
>>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>>registrations.vins;
>>
>>But cannot replicate this in SQL server?
>
> Hi Sam,
> You're almost there. The Transact-SQL version of this would be
> DELETE warranty
> FROM warranty
> INNER JOIN registration
> ON warranty.BBMQCE = registrations.vins
> Yes - you only need to drop the .* !!!
>
> However, the above is proprietary code that will not port well to other
> databases. If you want portability, use the ANSI-standard delete syntax
> instead:
> DELETE FROM warranty
> WHERE NOT EXISTS (SELECT *
> FROM registration
> WHERE warranty.BBMQCE = registrations.vins)
> (both queries untested - beware of spelling errors!)
> Best, Hugo

DELETE FROM warranty
WHERE EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches|||On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:

>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches

Hi Andrey,

Good catch! Thanks for correcting my mistake.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
>
>>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches
>
> Hi Andrey,
> Good catch! Thanks for correcting my mistake.
> Best, Hugo

You're welcome :)

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Records!

Hi,
I am trying to delete records from a table when a specified column encounters null values in them. Could you please let me know how!
ThanksRefer to your other post (http://www.dbforums.com/t926583.html) in this forum.

You can use DELETE FROM tablename WHERE Columnname=null and refer to books online for more information.|||columnname is null

instead of columnname = null|||Oh yeah, thanks for the make-up.