I have a really simple query I'm trying to execute. I want to replace all instances of int X (8) in Column A (LastActivityID) with int Y (27) but the following SQL returns the said error. I understand the error, but not sure how to script the SQL differently to get the intended result. Thanks in advance.
Code Block
UPDATE Item SET LastActivityID = 27 WHERE LastActivityID = 8 Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.
(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249
(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'
It says query executes successfully but returned no rows.
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.
Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
I'm a total newb to SQL so I apologize if I butcher the description of what I'm looking to do :) Hopefully the example makes more sense. Is it possible for a single record in 1 table to contain relationships to several records in a different table?
What I've got so far is 2 tables - Hardware and Software, with ID Specifying primary keys HardwareID and SoftwareID respectively. the hardware records are for computer details and the software obviously are for programs. What I need to be able to do is pull up a hardware record and be able to see what software is installed on it, and vice versa pull up a software record to see which computers it's installed on. The problem I'm running into is that a computer can have multiple programs installed on it and a program can be installed on multiple computers, so I don't know how to create that relationship to account for that.
What I'd like to see is in the Hardware table a column for SoftwareID that has a foreign key relationship to the SoftwareID field in the Software Table, and vice versa....My question though is is that possible to do and have potentially multiple separate records it links to from that same column field? I might have a computer with say Windows XP Pro, Office 2003 Standard, Adobe Acrobat 8 and a proprietary rate calculator program that i need each to be displayed with their details when I open that computer's record. Or on the software side if I need to see which computers a license is already installed on then I want to make sure I can pull up the full list of computers.
And finally if what I'd like to do isn't possible as I described it...any recommendations for a better way?
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
i have a total of 5 records to update. one being a summary row and the other four being 1,2,3,4 record ids. is there any way to update these 4 records w/o using 4 seperate updates???
First off, Ive been asking a lot of questions lately and I want to sincerely thank those who have been posting VERY helpful replies. I have learned much in these past few weeks.
That being said I have hopefully one of my last questions before this project is complete.
Im running a DTS that imports data from a flat text file and updates 6 different tables. In one of my tables, "PERSON", I only want the records to be inserted or updated if the FCN field is new, or unique.
Here is my code that updates the table (without regard for uniquneess):
INSERT INTO PERSON (FIRST_NAME, LAST_NAME, MIDDLE_NAME, FCN) SELECT "FIRST", "LAST", "MIDDLE", FCN FROM DATAGRAB WHERE RECORD_DATE = convert(varchar(8), getdate()-1, 112)
How can I convert it to only update records with a unique FCN value?
Ive posted on here many times before and got the answers, so hopefully this time will be like the rest...you guys are SO good at SQL.
I have a database that has lots of fields. I use some specialised email marketing software that allows me contact each record. What I have done in the past is export the contacts i wanted to email and then format the columns like this: (all done in excel)
EmailAddress Website Salutation Website1 Website2 Website 3 info@eg.co.uk www.eg.co.uk David eg.co.uk Eg.Co.Uk Eg test@red.co.uk www.red.co.uk Sue red.co.uk Red.Co.Uk Sue
After each list looks like this, i then import this data into Access and from there our email software pics up the data and emails accordingly.
Now here comes the problem...
We have decided that we now want everything done within our sql database. Which means we want clickthroughs recording and open rates etc all appearing in our database. (we have all this sorted out)
However we dont have Website1, 2 or 3 fields in our database. We know that they need to be included in our db somewhere but with half a million contacts we don't want to do it one by one obviously.
We want a simple where we export all our contacts and then format them so they include website1, 2 and 3. THEN we want to import them, simply updating the contacts with that new data. (Or is there a SQL way of creating temp tables so these fields dont even need to be included in the db?)
I hope i managed to explain myself in way you can all understand.
I have an Excel file of 1362 rows and 30 columns. I need to load itinto my database to update addresses and phone numbers. Each recordhas its own ID number, which is defined in the Excel file and in thedatabase. The database is Millenium Version 7.2.1.1. I can't reallyfind any signifigance between any of the records. Is there a way I canjust load them into my database and update each record? Maybe by usingthe ID number?Dane WinklerDatabase SpecialistSt Vincent CollegeJoin Bytes!
I'm having a bit of problem putting together a query that will update records in one table from another table. I've got 2 tables lets call them tblA and tblB.In tblA there is EID(int), QID(int), OID(int) and in tblB OID(int) and QID(int). Also there is an input parameter @EID.What I want to have happen is when someone inputs @EID then tblB gets updated from tblA. To give you a heads up there are no PKs or FKs in either of the tables.If there is an OID in tblA it takes the QID from tblA and places it in tblB where OID from tblA and tblB match.Hopefully this makes sense I thought that I could do something like this:CREATE PROCEDURE test3(@EID int)AS UPDATE tblUsedSET QuestionID = (select QuestionID from tblExamQuestions where ExamID = @EID)WHERE OrderID = (select OrderID from tblExamQuestions where ExamID = @EID)GOBut I get an error that says that there are to many records being returned by the subqueries. Winston
I have a table with 5 columns (col1, col2, col3, col4). I want to do is: 1) To check if any two records are duplicates (if the the values in col1 of record A are identical to Record B, two records are considered as duplicates);
2) if two records are duplicate, I want to mark Record B as "Dup" in col4 ;
3) move the data of Col2 of Record B to col3 of Record A.
I have tried to use CURSOR for the job. I would appreciate if anyone can give me some hints for updating records using Cursor.
My script looks like this: CREATE PROC Mark_duplicate AS DECLARE @var1_a, /* To hold the data from Record A */ @var2_a, @var3_a, @var4_a, @var5_a,
@var1_b, /* To hold the data from Record B */ @var2_b, @var3_b, @var4_b, @var4_b,
/*** Create a CURSOR ***/ DECLARE Dup CURSOR FOR SELECT col1, col2, col3, col4 FROM TableA ORDER BY col1, col2 FOR UPDATE OF col1, col2, col3, col4
/**** OPEN the CURSOR ****/ OPEN Dup FETCH NEXT FROM Dup into @var1_a, @var2_a, @var3_a, @var4_a WHILE ( @@FETCH_STATUS =0 ) BEGIN FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, @var4_b WHILE ( @@FETCH_STATUS =0 ) BEGIN If ( @var1_a = var1_b ) THEN . .Updating statements . . ELSE SET @VAR1_a = @var1_b, @VAR2_a = @var2_b, @VAR3_a = @var3_b, @VAR4_a = @var4_b FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, var4_b END END CLOSE DUP . . .
I have a table that has 4+ million records. I need to update those records. I am facing some performance issue. Can someone please advice?
update stage set batch_status = 1 where update_status = 0
Update transaction Set aId = s.aId, b = s.b,
from stage s Where s.aId = transaction.aId and s.batch_status = 1
Update stage Set update_status = 1, batch_status = 2
where
batch_status = 1
When I run the above query with "set rowcount 1000", it runs in one minute. When I run the query for "set rowcount 10000", it runs in 1 hour 56 minutes. Can someone help me to optimize it?
Here's a problem that I can't find anyone else has run into. I'm usingAccess and SQL Server, but the theory would be the same for any db.I have a large number of tables that contain linked records(intersection tables mostly). In the interest of space, I'llillustrate an example:tblStudents (ID, Name)tblTeachers (ID, Name)tblClasses (ID, Name)tblEnroll (StudentID,ClassID,TeacherID)I have about 10 people who each use a separatecopy of this database (in access). I want them (at the end of eachday) to be ableto update all the records that they entered that day into a databasethat I have setup on a server (SQL Server 2005). Both databases havethe exact same structure.Caveat 1: All of the classes, students, and teachers are not the sameon each database, but the server database should contain all of them.Caveat 2: There is no way for the clients to automatically insert intothe server, they are offsite and out of range.Herein lies the problem: when a record is inserted into the server froma client, all of the links are lost since the ID will be different onthe server that it was on the client.I don't think a simple update / insert query will work, and most db'sand languages don't play nice with recordset appends.What are your thoughts??
I have a following problem: I€™m importing records from an Access table into a SQL Server Table. I€™m using lookup to determine if a record already exists in the SQL Server table and in that case I should update the record if it was modified.
I thought of using OLE DB Destination for new records (done, works fine) and OLE DB Command Transformation to update the modified records. It all works but the thing that bothers me is that my table has approx. 40 columns so my OLE DB Command is very long (update table set col1 = ?, col2 = ?, col3 = ? €¦€¦). The other problem is that I€™m always updating all the columns even if only one column was modified.
Is there a better way to update the existing records?
I want to use SSIS in order to synchronize data. The OLEDB and ADO.NET Destination Adapter just inserts the rows read from the source. Since I have PK Constraint on destination, the tranform fails. How can I ask to update the records in destination DB?
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
HAX603 JULY 1 OCTOBER 31 HAX317 DECEMBER 1 DECEMBER 31 HAX317 MARCH 1 MARCH 31 HAX317 July 1 July 28
[Code] ...
Final OutputÂ
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + Â startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + Â endmonth)AS Season from seasons;
Hi, I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??
CREATE PROCEDURE PublishSchedule ( @SiteCode smallint, @YearMonth int ) AS
DECLARE @Active bit IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 ) BEGIN UPDATE CabsSchedule SET Active=1 WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth END GO
All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid. Dim sDate As DateTime sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure..... Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num VARCHAR(256),@date SMALLDATETIME,@contact VARCHAR(256),@notes VARCHAR(8000),@media VARCHAR(256) ASBEGIN DECLARE @errCode INT BEGIN TRAN -- UPDATE THE RECORDS UPDATE dbo.tblData SET fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media WHERE fldNum = @num <sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath
I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?
I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it. How can I get it done ?
