Hello,
I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
I am a beginner in Ms-Sql,so kindly help me with this query:-
Following is the table:-
Name Phone email
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com
How can i manage to delete only 2 rows out of these 4 rows
I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:
John Smith 1
John Smith 1
John Smith 1
Jane Doh 1
Jane Doh 1
Al Johnson 1
but I need it to return totals like this
John Smith 3
Jane Doh 2
Al Johnson 1
This is the query I am using:
Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID)
from Player INNER JOIN
PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID
join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID
join Action on Treatment.Action_ID = Action.Action_ID
group by Player.First_Name, Player.Last_Name, Action.Employee_Id;
I have an "insert into" statement that creates two identical rows in a table, with this statement:
delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values])
This is the code-behind that performs the insert:
Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using
I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc
How can I render the differences in red in RS?
I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.
View 3 Replies View RelatedCREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies View RelatedWe have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
View 4 Replies View RelatedI run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
Hi,
I have a table called Past with 4 columns(Number,WW,Goal,Eng) and in Number column has 53 rows, 1,2,3 - 53. Now i need to update the 53rd row with the following code and it works. Since i dont know how to combine this i update them separately.
Update Past
set WW = ('WW'+convert(varchar(10),datepart(wk,getdate())-1))
WHERE Number = '53'
Update Past
set Goal = (Select Eng_Goal from AverageEngTime)
WHERE Number = '53'
Update Past
set Eng = (SELECT ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
WHERE Number = '53'
Now, whenever i execute, I want my row 52 to be updated with data from row 53 and row 51 with data from row 52 and so on until row 1 with data from row 2. Meaning i just want to update new data for row 53 and other rows should be updated with the data from next line. How do i do that? Please help..Thanks
Hi,I'm using table it has 100 rows, how can i update 10 or 15 rows at thesame time with single query. In real case, if user enters "5" intextbox it means that the "5" rows will be updated.In SAS i was using 'outobs' {outobs = n} which means n rows will beaffected.Thanx.
View 7 Replies View RelatedAny idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
Hi
I would like update the rows of table 1 with the values in table2 see below based on a matching ID. I don’t want to update the ID field as this would be a violation of the primary key I assume as ID is a primary key, just the FirstName and SecondName fields need updating.
Table1
(ID, FirstName, Surname)
Table 2
(ID, FirstName, Surname)
Any ideas?
Cheers
ok my data base has 4 columns id,fname,lname,email and 3 rows 1,2,3
I made a simple update form that accesses the data and displays all three rows with the data in them:
<cfquery name="QUIZ" datasource="test">
SELECT id,fname,lname,email
FROM info
order by id
/cfquery
<HTML>
<HEAD>
<TITLE>Update an Employee</TITLE>
</HEAD>
<BODY>
<H1>Update an Employee</H1>
<FORM ACTION="databaseupdater.cfm" METHOD="POST">
<cfoutput query="QUIZ">
<INPUT TYPE="hidden" NAME="id" VALUE="#id#">
<P>
First name:
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(fname)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(lname)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="email" SIZE="15" MAXLENGTH="30" VALUE="#Trim(email)#">
<P>
</cfoutput>
<INPUT TYPE="submit" VALUE="update">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
you can use this form to send the updated data to databaseupdater.cfm:
<CFQUERY
DATASOURCE="test"
>
UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
</CFQUERY>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
Thank You
</body>
</html>
the problem Grieg RN is each column in the database gets updated with all 3 rows of data in each column in stead of seperating the data in its correct row and column. I don't know much about the problem im just starting out please help. Thanks
Hi all,
I need to write an SQL statement to update multiple row (all) as follws and can just not get it right please help.
I have two fileds - "Tilte" and "Title1" in my "notes" table.
I would like to run through the entire table and replace the information in "Title1" with that of "Title", also I need to change a character whilst doing so , ie change a "=" to a "-"
Example
Title - "Will is coming to town = Paul"
need to copy this to Title1 and it must change to...
Title1 - "Will is coming to town - Paul"
Running MS SQL2000 SP4
Help appriciated
Thanks
I have to update the domain name on our email database. Is there a simple update I can use to change firstname.secondname@olddomain.com to firstname.secondname@newdomain.com. I've got about 2800 records to update and I don't fancy either loosing them all by doing a dogy update query or doing it one at a time.
Any help would be greatly appreciated................
We have a table and it is having records with for example “Chase“, now we don’t want those “ “ and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?
Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here
declare @tbl varchar(200)
declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'
open cur
fetch next from cur into @tbl
while @@FETCH_STATUS = 0
begin
print @tbl
--exec sp_spaceused @tbl
fetch next from cur into @tbl
end
close cur
deallocate cur
Can you please send me the logic?
It will be more appreciable.
-Thanks N Regards,
Kanthi.
My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.
I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.
SQL Server 2005
Table (many columns left out for simplicity)
ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....
Typically there is approximately 250 records per HdrID.
I am trying to do an SQL UPDATE without using my usual solution of writing vb code!
I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.
ie;
ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00
In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.
Jake
IUpdating existing rows in a SQL server 2005 destination table
--I have a CSV file and every row needs to perform an update on a production table
The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ???
That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow??
thanks in advance
Dave
We have a table and it is having records with for example €œChase€œ, now we don€™t want those €œ €œ and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?
Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here
declare @tbl varchar(200)
declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'
open cur
fetch next from cur into @tbl
while @@FETCH_STATUS = 0
begin
print @tbl
--exec sp_spaceused @tbl
fetch next from cur into @tbl
end
close cur
deallocate cur
Hi All,
In SQL Server Database I have a table, which DO NOT have any primary Key field.
How can I update rows distinctly?
There is a possibility of having duplicate rows.
Do we have any alternate in SQL Server like ROWID (in ORACLE).
Regards
Sidheshwar.
Hello guys,
I have several rows I need to update OR insert. Right now, the code deletes all the "possibles" rows and then inserts.
I don't like this way because the index would be recreated each time it does do a DELETE/INSERT action.
Is there any "good practice" when inserting/updating rows? Any better way to do it?
Thanks.
I am trying to run a simple update statement that updates around 1 million records on SQL Server 2005. For example:
update customers
set CustCode='AAB'
where CustType=72
I would like to update the table WITHOUT locking. In this case, there is no need to have "all or nothing" transactions. If it does a partial update and then fails, it's ok to only have half the records updated.
The server is using up a lot of resources creating and releasing the locks. Plus users are getting locked out of the records during the update. I know this is by design, but in this case it's OK. I know I can use the "set transaction isolation level READ UNCOMMITTED" statement to fix the select statements from getting blocked, but there are way too many places that would have to be changed. Plus there are other updates to this table that need to be locked.
So here is my question: Is there a way to do a transaction-less update?
Thanks,
Stuart Fox
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
The Trigger must verify one field for eveery row updated and roll back any incorrect ones. I have tried different ways but I can't figure out how to make it work.
View 10 Replies View RelatedI'm tracking sales reps and the companies they represent. I have 2 tables. A header table that has the SalesRep (a key field) the name, phone, address, etc.A line file that will have the sales rep multiple times, each line having a vendor they represent.
SalesRepHeader table:
SalesRep name phone VendorList
Bob Bob_Smith 111-222-3333
John John_Young 123-456-56789
Mary Mary_Kerns 567-876-98765
[code]...
Whenever the SalesRepLine file is added to or updated I want the header file field VendorList to be updated with a concat of the vendors for this sales rep so the SalesRepHeader file would look like this.
SalesRep name phone VendorList
Bob Bob_Smith 111-222-3333 Samsung, Kenwood, JVC
John John_Young 123-456-56789 Apple, HP
I need the update to be automatic.
Is there any way where I can add the MySQL Server as a Linked server to MS SQL server?
Is there any way where I can update a table in MySQL server based because a row was updated in MS SQL server using a trigger?
Thanks,
Amit
I have a table with 4 fields,
Startdate, rec_num , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
I will like to update recursive_date to emulate the recursive_number and recursive_value fields which specify every 3 days. The recursive_value field can also be w to specify weeks, m to specify month or y to specify years.
So my value in the recursive_date field should be updated as followed
Startdate, rec_number , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/05/2014
04/02/2014 3 d 04/08/2014
I want to update multiple rows in one table
DDL of 3 tables
CREATE TABLE [dbo].[appl](
[app_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL)
CREATE TABLE [dbo].[appl_party](
[prty_id] [numeric](9, 0) NOT NULL,
[app_id] [numeric](9, 0) NOT NULL)
CREATE TABLE [dbo].[party](
[prty_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[lockbyid] [char](8)
I want to update muliple rows in table "party" for column "lockbyid"
below is the update query with which i can only update one row but i need to update multiple rows in party table
update party set LOCKBYID ='abcd' where prty_id in
(select distinct prty_id from sappl_party where app_id in (Select appl.app_id
FROM appl INNER JOIN appl_party ON appl.app_id = appl_party.app_id where appl_party.prty_id=1234))
and LOCKBY_USR_ID is null
what is wrong with this statement?
Update services
Delete
where ticketnum = '0'
error message: incorrect syntax near keyword delete
Hi,Does SQL support update to multiple rows where values coming from asub-query?e.ginsert into TABLE1select column1, column2, column3 from TABLE2This is perfectly valid, assumes TABLE1 has only three columns,column1, column2, column3.My question: Is there any way to UPDATE values to TABLE1 similarly?something likeupdate TABLE1 set column1= ?, column2= ? , column3= ?where ................ TABLE2...........OR is it that, sql allows only UPDATEs with one set of values to nrows.Can any one throw some light on this.-Thanks and Regards,Maymon.
View 3 Replies View RelatedI am trying to determine the next registered session of a student so Ican calculate the number of skipped sessions.Scenario: I have a student registration summary table. One row foreach student and the student's registered session. I want to update agiven row with the next higher registered session (into a field callednext_registered_session_skey if the row exists). I can then use thediff of the skeys to determine how many sessions the student skippedfor each registration period.Example: Student X registers each fall for one session for 4 years.The file might look like:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100null123456789 200301 104null123456789 200401 108null123456789 200501 112nullI need to update the NEXT_REGISTRED_SESSION_SKEY so I end up with:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100104123456789 200301 104108123456789 200401 108112123456789 200501 112nullI can then say SESSIONS_SKIPPED = NEXT_REGISTRED_SESSION_SKEY –SESSION_KEY (logically speaking, not syntactically)This is what I have so far as example:UPDATE F_REGISTRATIONSET NEXT_REGISTERED_SESSION_SKEY = (select top 1 nextr.session_skeyfrom f_registration rinner joinf_registration nextron r.student_skey = nextr.student_skey and nextr.session_skey[color=blue]> r.session_skey[/color]order by r.session_skey desc)WHERE STUDENT_ID = '577665705';SELECT student_skey, student_id, session_id, session_skey,next_registered_session_skey, * FROM F_REGISTRATION WHERE STUDENT_ID= '577665705' order by session_skey descRESULTS:STUDENT_SKEY STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTERED_SESSION_SKEY125137 577665705 200404 309 311125137 577665705 200403 308 311125137 577665705 200402 307 311125137 577665705 199804 285 311125137 577665705 199803 284 311125137 577665705 199802 283 311125137 577665705 199704 281 311TIARob(I restricted with the where = ‘577665705' so I did not have to waitto update all the rows)
View 6 Replies View Related