Hi All,
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar]
(2)-ReceivedTime [datetime]
(3)-Status [char].
Now here how to remove duplicate entry of same mobile number to MobileNumber field?
Regards
Shaji
How can i keep only one record from duplicate entry.
Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14
Here i want to keep only the top record and want to delete other 2 rows.
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Thanks,
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View 10 Replies View RelatedWe have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View 4 Replies View RelatedI'm trying to modify a specific row in a table.
$paper_id = 2A
$query = "UPDATE book SET paper_id='$paper_id', title='$title'";
I'm getting this error <<query failed: Duplicate entry '2A' for key 'PRIMARY'>>
Structure:
Column: paper_id
Type: Char
Length: 20
Deafult: None
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
View 1 Replies View RelatedHello All,
I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.
I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.
This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.
Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
Can someone please help me solve the following error? I am not sure of the version my hosting company is using and where to get the manual.
Could not add the entry because: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-id, title, entry, date_entered) VALUES ('testing', 'testing', . The query was INSERT INTO blog_entries (blog-id, title, entry, date_entered) VALUES ('testing', 'testing', 'testing'.
PHP Code:
if ($dbh = mysql_connect ('localhost', 'blu6592_money', 'rich')) {
print '<p>Successfully connected to MySQL.</p>';
if (mysql_select_db ('blu6592_myblog')) {
print '<p>The database has been selected.</p>';
} else {
die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
}
$query = "INSERT INTO blog_entries
(blog-id, title, entry, date_entered)
VALUES (0, '{$_POST['title']}',
'{$_POST['title']}',
'{$_POST['entry']}', NOW())";
if (mysql_query ($query)) {
print '<p>The blog entry has been added.</p>';
} else {
print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}
} else {
die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}
mysql_close();
Thanks in advance.
(I don't post here often, so in case I'm violating long-standing taboosof this newsgroup, I apologize in advance for calling a relation atable, using nulls, and other ignorant, destructive, and comtemptibleterminology.)I have a table that's keeping a sort of running log of different typesof changes to pieces of data. The table has a foreign key of the databeing changed, the foreign key for the type of change occuring, someinformation about the change in a couple more columns, and a timestampfor each entry. So it's:dataIDeventIDeventInfotimestampWhat I'd like to do, if at all possible, is a single SQL query that,given a dataID, returns the most recent eventInfo and timestamp foreach eventID. Is this possible?Many thanks.-Eric
View 3 Replies View RelatedAnyone Got any other advice?
http://www.dbforums.com/t993214.html
"High priority system task thread: Operating system error Exception 0xAE encountered."
we are seeing this on one of our development servers, running SQL 2005 Enterprise SP1. The symptom is that after the sql service has been running a while (order of days), this error starts getting written to the sql server log file over and over, and eventually the logfile fills the drive.
Apparently it's fixed by this hotfix, but our ops team doesn't want to install this - they'd rather just cycle the service and delete the log when it happens.
I was wondering if anyone else has seen it might know the root cause so we might be able to workaround it without cycling the service. I know, lame question. The correct answer is "install the hotfix" or "install sp2" but our ops team won't do that for now...
www.elsasoft.org
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to?
Thanks!
Hello,
I have a table of names/dates as such:
Class 1 1/1/2007Class 1 1/3/2007Class 1 1/5/2007Class 2 2/1/2007Class 2 2/3/2007Class 3 3/1/2007
What I want to do is select only the nearest entry from the list for each distinct class, so the results are like this:
Class 1 1/1/2007Class 2 2/1/2007Class 3 3/1/2007
So basically, I want the first upcoming class in the list for each distinct class name. How do I do this? I'm using SQL 2005 Express.
Thanks.
How do i do that... in sch lab, the lecturer said: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Try SqlDataSource2.SelectParameters("Title").DefaultValue = txtProductTitle.Text GridView1.DataBind() If GridView1.Rows.Count > 0 Then //if no repeated Label1.Text = "Existing Record Found. Please enter another Software." Else 'SqlDataSource1.Insert() 'Label1.Text = txtProductTitle.Text & " successfully added to the database." End If Catch ex As Exception Label1.ForeColor = Drawing.Color.Red Label1.Text = "An error occured!" & ControlChars.NewLine & ControlChars.NewLine & _ ex.Message.ToString() End Try End Sub but its not working for me... the GridView1.Rows.Count is always 0, if there is a repeat, it should be > 0
View 4 Replies View RelatedHi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
View 4 Replies View RelatedHi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View 4 Replies View RelatedHere's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks.
Function DoesUserExist(ByVal userName As String) As Boolean
Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
Dim Cmd as New SQLCommand(queryString, sqlConnection)
With Cmd.Parameters
.Add(New SQLParameter("@username", username))
End With
sqlConnection.Open
Dim blHasRows As Boolean
Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.RecordsAffected > 0 then
blHasRows="True"
else
blHasRows="False"
End If
Return blHasRows
End Function
How can I select from a table the newest entry.I'm inserting in a table user info and then want to get the users id number
View 9 Replies View Relatedhi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .
Ahmed
Hello,
I am trying to select the last entry (by date) for each category in a table.
For example, if my table had the following fields;
id, category, product, datePosted,....
...how would I select the last product for each category posted by date?
Any guidance is appreciated.
Thanks,
AC
Probably a simple solution, but can't find it my brain right now!
Task:
To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.
What's the quickest/cleaniest way to do this ?
thanks,
Clayton
MSSQL2K
SP4
Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.
--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))
insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')
select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f
Current results:
132006-01-01 01:01:01.000Insert
132006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
222006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update
Desired results:
112006-01-01 01:01:01.000Insert
122006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
212006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update
TIA, CFR
Hi all,
Newbie here, just wanted to get that out of the way first
and foremost.
Im trying to set up a DB in SQL 2000 that allows me to enter
lots of text into a column.
I read one site where it said to use the NTText datatype with a length
of 16. I did that, but everytime I enter alot of text into that column
I get <Long Text> and I dont know how to output that or even
read it.
Do any of you guys know how to either:
1. get the text out of the <long text> or
2. a better way to store that much data.
Thank you in advance!!!
REZ
i have data like...
userid startdate in_out
1 2011-11-20 9.30 1
1 2011-11-20 10.30 0
1 2011-11-20 12.30 0
1 2011-11-20 12.45 1
1 2011-11-20 6.30 0
See 1-Entry 0-exit for 2011-11-20 12.30 is exit but entry time of that exit is missing....
Can i insert same exit value for entry?
Hey there, could someone please help me!!
I had created a database in sql server but since then installed windows xp pro and now when i go through the microsoft sql server dsn configuration the database is not valid and therefore i cannot change the default database as it says it doesn't exist.
So then when i type in command prompt use 'db' it says it doesn't exist but when i try to create 'db' it says that the database already exists.
I get the error message, could not locate entry in sysdatabases for 'cti' (database name).
When i look in mysql - data - along with master etc there is my cti.mdf and log files??
I'm stuck???
Is it that the files just aren't registering through sql server???
Please help!
Hi,
I have the following situation:
I have a table that is log of transactions of useractions. Each time a userstatus changes, a new entry is made into the table.
The table has (currently) about 4 million records, and keeps growing each day with thousands of records. It logs the status of about 150 users (currently active).
What I now want is to get the log-entry from all the distinct users that have a log-entry on the latest day (so current day, 'where TimeStamp > 2008-04-08').
(extremely simplified) Example:
(date is y/m/d)
USERID TIMESTAMP STATUS
----------------------------------------------------
... (thousands and millions of records above)
Ed 2008-04-07 18:00:00 Logged_Out
Jim 2008-04-07 18:30:00 Blabla_Status_5
Jack 2008-04-07 19:00:00 Logged_Out
Jim 2008-04-07 19:30:00 Logged_Out
Jim 2008-04-08 06:00:00 Logged_In (<< notice new day)
Jim 2008-04-08 06:01:00 Blabla-Status_1
Bob 2008-04-08 06:03:00 Logged_In
Fred 2008-04-08 06:05:00 Logged_In
Jim 2008-04-08 06:08:00 Blabla-Status_2
Jack 2008-04-08 06:12:00 Logged_In
Fred 2008-04-08 06:20:00 Blabla_Status_5
Jack 2008-04-08 06:12:00 Logged_Out
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jack 2008-04-08 06:12:00 Logged_In
Jack 2008-04-08 06:12:00 Blabla_Status_7
----------------------------------------------------
The result should be:
USERID TIMESTAMP STATUS
----------------------------------------------------
Jack 2008-04-08 06:12:00 Blabla_Status_7
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jim 2008-04-08 06:08:00 Blabla-Status_2
Bob 2008-04-08 06:03:00 Logged_In
----------------------------------------------------
(How) can this be done in one stored procedure?
I have an online store with products and categories. The requirements are: Each product can be in one or more categories; a product must be in at least one category.
To accomplish the first requirement, I have a Product table, Category table, and a many-to-many Product2Category table. (Probably not relevant, but a requirement is the Category table is self-referencing to provide sub-categories.)
My question is for the second requirement that each Product be in at least one category. My assumption would be to add a column to the Product table called "ParentCategoryId", which is a foriegn key of the Category table's identity column.
What would be the best approach to ensure a product is in at least one category?
Thank you.
While i execute the below query ,Entry is put in status_report table.But the same if i put in scheduling (Sql Agent) no entry is made in the table and the message success is shown in "Agent Job Activity".
Purpose of the sp is to transfer data from One Server to Another Server.
But if see in the Destination Tables No entry is made.
CREATE procedure RBI_Control_sp
as
begin
set nocount on
begin try
BEGIN TRANSACTION
insert into fin_wh..Status_report
(
[object_name],
row,
st_date
)
select
'Data Tranfer',
'Inprogress--',
getdate()
--Data Transfer From the Live Server [RamcoFin02(scmdb)] to Dw-Server[Ramcovm392(fin_ods)]
exec fin_ods..RBI_Data_Transfer_sp
--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp
Update fin_wh..Status_report
set row= 'Success',
[error_message] = 'No Error',
end_date= getdate()
where row= 'Inprogress--'
COMMIT TRANSACTION
end try
begin catch
ROLLBACK TRANSACTION
Update fin_wh..Status_report
set row= 'Failure',
end_date= getdate(),
[error_message]= cast(error_number() as varchar) + '***' + error_message()
where row= 'Inprogress--'
end catch
set nocount off
end
Txs in advance