|
Hi jijo jose,
Thanks for your response, but your solution deletes all records which are duplicate.even table does n't contain one value.
i want only delete duplicated records other than first value'
ex:
Sample Table Contains records are
sno sname
10 XXX
10 XXX
10 XXX
20 YYY
20 YYY
But after deletion of duplicate records i want records in table like this : sno sname
10 XXX
20 YYY
|
|
|
|
|
in continuation to my earlier solution....
select * from tblA where newcol IN (select min(newcol) from tblA group by sno having count(*) > 1)
should give first occurance of the each duplicate row.
Note :
newcol is the new identity colum
Regards
KP
|
|
|
|
|
hi,
I thought, you wanted to delete all the dubplicated one including original,
Then do this in your query analyser.........
1 st step
----------
alter table table1 add [id] int identity
2nd step
----------
delete from table1
where [id]
not in
(select min([id]) from table1 group by col001)
it will delete all the duplicate records from table1
regards
Jijo 
-- modified at 9:36 Thursday 29th March, 2007
jijo jose
|
|
|
|
|
Add new column to the table and make it IDENTITY
so that each row can be identified Uniquely.
Regards
KP
|
|
|
|
|
You can use the temp table, put the only exist records into the temp table then you can delete the original table's records use the condition with the information of the temp table.
onestone
|
|
|
|
|
I have write a Strore Procedure with Encryption.
for Exmaple
alter Proc GetAll
with encryption
as
select * from test1
after completing SP Encryption.
I am not able to again modify My Store Procedure.
please help me.
Pavan Pareta
|
|
|
|
|
|
|
This is the accepted wisdom, but as with a lot of things that are accepted wisdom, it's not always true. Take a quick search on Google for decrypt Sql Server procedure, and you get numerous hits - some of which tell you how to decrypt procs. I've provided a link to one above.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have a table with a parent-child relationship in a MySQL database:
id
parent_id - points to id
other columns
I can easily extract all rows belonging to a particular parent. For example:
SELECT * FROM table WHERE parent_id=5
What I'm trying to do is extend this to not only get all rows belonging to a particular parent, but also get a count of each row's children (the grandchildren). I don't need any further generations. Other than retrieving the children and iterating through the rows and fetching the counts seperatley, I've been getting into a good muddle with joins and self-referential joins.
I'm hoping that some kind soul has a few suggestions!
|
|
|
|
|
This seems to work on my system (not MySQL).
SELECT * , (SELECT COUNT(*) FROM tblTasks WHERE parentTaskID=T.taskID)
FROM tblTasks AS T
WHERE T.parentTaskID=5;
Don't know whether MySQL will support the (SELECT ...).
Hope this helps.
|
|
|
|
|
That is perfect, thank you! And a whole lot simpler than my left joins
|
|
|
|
|
hello All dear,
I want to create job which sends email every 10 minute by retreiving fldEmail[email id] from table.
Develop2Program & Program2Develop
|
|
|
|
|
Navneet Hegde wrote: I want to create job which sends email every 10 minute
Using what you will send mail ? procedure ?
|
|
|
|
|
Hi dear,
I have a table consisting name and dateofBirth and email
now i want to create job that send's email to particular name on that date
Develop2Program & Program2Develop
|
|
|
|
|
for your job to be done you need a continuous scanning of your database table.......
you can do two things....
1. write a program for scanning your database every 10 minutes and expose your program as Windows Service....the application will start as soon as your OS boots
or
2.write an application where you have to set up a Timer control to scan your database....make its interval to 10 minutes(interval=1000 means 1 second)...here you have to manually start your application unlike Windows Service... or if it is a EXE file you can have it in your Start-up folder.......
Tirtha
"A man can ride on your back only when it is bent....."
|
|
|
|
|
dear,
Yes T' you are right, but I want to do it using Job Scheduler [SQL Server 2000] I hope you help me with this idea
1] I create new job in that I execute my Stored procedure which returns a emailaddress
2] In that I create 2 job which execute on suceess of Job-1
In job-2 I execute xp_sendmail Stored procedure [which send's mail from sql server 2000]
Now my problem is the how can I provide the email address from job-1 to sendmail's @receiver parameter of Job-2
Develop2Program & Program2Develop
|
|
|
|
|
hi
I have following problem.
i'd like to use the MAX() function to get the highest ID from a table.
i've used is lik this:
<br />
INSERT INTO Table<br />
(ID, Employee, Project, Date, ...)<br />
VALUES (MAX(ID) + 1,@Employee,@Project,@Date, ...)<br />
but this is not working. i've searched a bit in the internet that it's not possible to put functions into VALUES.
so i'd like to know another way to solve this problem.
thanks in advance
greetz pdluke
PS: sorry for my bad english
|
|
|
|
|
You could either make the id an identity column, or you can get the value using a sub select:
insert into A
(id, ....)
values
((SELECT MAX(ID) FROM A), ...)
|
|
|
|
|
yes an sub select is one possibility, but I'm
using MSSQL and an sub select is not supported like in MySQL.
|
|
|
|
|
Why don't you make the ID a primary key and set its Identity to true, then it will auto increment by 1.
|
|
|
|
|
the problem is that I'm using this database via a WCF-Service.
so multiuser accessibility is the main problem.
If 2 users are saving their date at the same, who would this be solved with assigning the IDs?
so I've tryed this with MAX().
I really don't know how it would act with the IDs.
I would be grateful if someone could tell me if this could work like you say.
greetz
pdluke
|
|
|
|
|
Accoring to this [^] mySQL support sequence numbers.
Regards
Andy
|
|
|
|
|
I use the following construct in my system:
INSERT INTO myTable(ID, Employee, Project, Date, ...)
SELECT MAX(ID)+1, @Employee, @Project, @Date, ...
FROM myTable;
and it works fine.
Hope it helps.
|
|
|
|
|
Hi,
Iam creating a stored procedure, in this stored procedure i want a view to be created whenever that store procedure is being executed,
Can i have a view created inside a stored procedure?
Thanks!
Gautham
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
