Update SubQuery(two Tables), Pls Advice Wats Wrong With The Query. 1 Hole Day I Spent Still Cant Figure
Oct 27, 2006
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
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 new to the SSIS world and am writing my first package. What the package is supposed to do is to take the XML export from one database and import into an identical database (production). There's no connection between the two DBs. During import, the SSIS package checks if there's any primary key values in a table already exist in the production DB and only imports the new records.
So here's the data flow of my package: XML Source -> Lookup -> Conditional Split -> SQL Server Destination. I'm doing a lookup against the same destination DB to check if a record (primary key value) already exists or not, and doing the condition ISNULL(id_out) in the Conditional Split. I really don't know if this is the correct way to implement it. The Lookup control errors out during debugging in Visual Studio. Here's what;s on the progress tab:
[Lookup [1745]] Error: The "component "Lookup" (1745)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (1747)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (1745) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
How do I get more specific error on what went wrong?
Hi,I have one table name: artcolumn:symbol_art price1 price2----------- ------- -------AG-0001 20 40AG-0001S null nullAG-0002 40 60AG-0002S null null....How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)thanks for any helpTom
I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1
I have wacked my head over this query I dont see anything wrong with it
My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!
i dont know if it is just because im tired or what. im trying to do a update one this table here is the stored procedure im usingALTER PROCEDURE Snake.UpdateSPlits @name nvarchar(50), @split nvarchar(50) AS Update accounts Set split_id = @split Where name = @name
RETURN Here is what im using to call it Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating Me.SqlDataSource1.UpdateParameters.Clear()
Dim name As String = Me.GridView1.SelectedRow.Cells(0).Text Dim Split As String = Me.GridView1.SelectedRow.Cells(1).Text
Dim pname As New Parameter("name", TypeCode.String, name) Me.SqlDataSource1.UpdateParameters.Add(pname)
Dim psplit As New Parameter("split", TypeCode.String, Split) Me.SqlDataSource1.UpdateParameters.Add(psplit)
Me.SqlDataSource1.Update() End Sub I keep getting one of 2 errors they areObject reference not set to an instance of an object. orone that says i had to many aurgements any idea what im doing wrong?
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
I have a small database in which Employee's are linked to "Tags". These tags can be red or yellow. So, I have 3 tables. Tag_Colors : tag_color_ID , tag_colors Employee_Table: employeeID , employee_name Tag_Table: tagID, tag_color_ID, employeeID, tag_notes
I need to be able to perform a query, where I can list employee's by the number of tags they have. I don't even know how to get started on this -- could anybody point me in the right direction? After I can et a query working, I think I can take it from there -- and get it to display on a web page.
Some have the same value in the 'subkey' field. I want to select all the records from the table that have their highest MAINKEY.
So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)
Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.
So the tables looks sort of lLike this:
ID SK MK 1 10 2 2 10 3 3 25 2 4 25 3
I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.
Hello, I have a query that returns a daily revenue figure. The query is as follows:
SELECT top 1000 ds.AcctCode, ds.TxnDate, SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue, --"MTD" = ?, --"YDT" = ?, ps.TC, CASE WHEN ps.Proj = 100 THEN 'New Account' WHEN ps.Proj = 200 THEN 'Current Account' END AS ProjStatus, ps.FSR, ps.SubmitRep1
FROM TxnRptg.dbo.tbl_DailySummary ds INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps ON ds.AcctCode = ps.Acct
WHERE MONTH(ds.TxnDate) = 1 AND Proj IN (100,200) AND TC = 'HV'
GROUP BY ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY ds.AcctCode, ds.TxnDate
--*********************************
TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?
I have run into a problem, I have 2 fields in my database, both key fields:
Table 1 ===== Field X <key> Field Y <key>
In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:
Enter the Codes you want: 1000 and 3000 and 8500
So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.
because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)
So I guess I want something like this:
SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD
^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.
I have an "Issues" table for my technicians. An issue can be on "hold"or "assigned".I want to get a count for each tech with a column showing number ofissues on hold and a column for number of issues assigned. It wouldlook like this --Tech Num_Assigned Num_On_HoldFred 3 10Carol 6 7I can get each column separately, but I want both in the same answertable!Is that too much to ask??? :)
I'm sure this is simple -- I'm just spacing it today. Assume the following:freq fruit---- ----10 apple5 apple7 banana6 pear3 pear2 orange8 orange13 kiwiHow do I find the highest [freq] for each [fruit] all at the same time, e.g.:10 apple7 banana6 pear8 orange13 kiwiThanks in advance.Mark D.
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
I have a simple table (SQL Server 2005) which stores employees clock-ins and clock-outs throughout the day:
+------------------------------------------+ ¦ Employee ¦ PunchDateTime ¦ ActionType ¦ ¦----------+------------------+------------¦ ¦ John ¦ 2014/03/26 08:00 ¦ IN ¦ ¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦ ¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦ ¦ John ¦ 2014/03/26 09:29 ¦ IN ¦ ¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦ ¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦ ¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦ ¦ John ¦ 2014/03/26 12:38 ¦ IN ¦ ¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦ ¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦ +------------------------------------------+
I want build a query that calculates time spent in and out. The end result should look like this:
+-------------------------------------------------------------+ ¦ Employee ¦ PunchDateTime ¦ ActionType ¦ TimeIn ¦ TimeOut ¦ ¦----------+------------------+------------+--------+---------¦ ¦ John ¦ 2014/03/26 08:00 ¦ IN ¦ - ¦ - ¦ ¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦ - ¦ - ¦ ¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦ 00:40 ¦ - ¦ ¦ John ¦ 2014/03/26 09:29 ¦ IN ¦ - ¦ 00:39 ¦ ¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦ 02:23 ¦ - ¦ ¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦ 01:26 ¦ - ¦ ¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦ - ¦ 01:07 ¦ ¦ John ¦ 2014/03/26 12:05 ¦ IN ¦ - ¦ 01:10 ¦ ¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦ 04:16 ¦ - ¦ ¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦ 05:07 ¦ - ¦ +-------------------------------------------------------------+
I need the total time spent IN and total time spent OUT, for each day.
The time spent will be calculated only for valid pairs of INs and OUTs, eliminating those that can't be paired (two or more consecutive INs/OUTs).
The results need to be by user, per one day. If the interval is longer than one day, the total time spent must be calculated for each day.
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
Is there a server variable which will tell me how long the server took in returning a particular recordset. I've looked through MSDN SQL 2000 articles and could'nt / did'nt see anything. Or perhaps there is another way? Thanks!
I am looking for some assistance with an update query that needs to link 3tables:This query ran and reported over 230,000 records affected but did not changethe field I wanted changed, not sure what it did.I did notice that the "name" in "GM_NAMES.name" was colored blue in QueryAnalyzer. Is it bad to name a column "name"?UPDATE ABSENCESset CustomerContactID = cicntp.cnt_idfrom absences, cicntpwhere (SELECT cicntp.cnt_l_nameFROM cicntp INNER JOINgm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOINAbsences ON cicntp.cnt_id = Absences.CustomerContactID)Next I tried this query which is still running after 75 minutes (on alaptop)update absencesset CustomerContactID = cicntp.cnt_idfrom absences, cicntp, gm_nameswhere gm_names.name= cicntp.cnt_l_nameAs you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.Absences.CustomerContactID is what I need updated, when finished it shouldmatch CICNTP.cnt_idGM_NAMES is a temp table and matches records in CICNTP.cnt_l_nameCan some of you school this newbie on the best way to do this?Thanks a bunch!
Hi,Apologies if this has already been asked, but I couldnt find a thread that asked exactly what I wanted.Im making an administration panel for a site where you can change various settings, options, and categories that data can fall into. When editing, adding or deleting a record i COULD make a trip to the database every time, but this feels very inefficient as I understand that establishing the connection is usually the biggest performance hit when querying a database.An alternative plan is for me to simply record the changes made in the panel and have a "save" button. When this is clicked, ONE database connection would be opened and all the data would be saved/updated/deleted as necessary. However, this would involve several "for" loops while the connection is open.The question is; which method would you recommend and why? And does having several "for" loops while the connection is open nullify the advantage gained by only opening one connection?Any advice would be very much appreciated. Thank you
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT * FROM tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13') ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id SET accrueamt='1348' WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13')
Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:
ID - int, identity Type - varchar(50) PurchaseNumber - varchar(50) SalesAmount - Money
ID Type PurchaseNumber SalesAmount 1 A 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 C 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 A 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:
ID Type PurchaseNumber SalesAmount 1 B 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 B 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 C 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.
I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.
The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.
I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments. TIA.
I am really stuck here, I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db, 1- Stages 2- Activities 3- Tasks 4- Subtasks Structure is like: Stages 1- SrNo (Unique) 2- Stage 3- StartDate 4- FinishDate
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages. I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
I need to update the status_date field in the journal table with the most recent status_date value in the transactions_batch_tmp.status_date column. However, when I run this query I get a syntax error: An aggregate may not appear in the set list of an UPDATE statement.
UPDATE journal SET status_date = max(tmp.status_date) FROM transactions_batch_tmp tmp, contacts c, products p WHERE c.emailaddress = 'test@yahoo.com' AND c.contact_id = j.contact_id AND j.product_code = 'EAENERGY' AND j.product_code = p.product_code
Update Table1 set Table1.Field1 = (Select Field1 from Table2 where Table2.Field2 = Table1.Field2)
SQL Mobile doesnt seen to support this, so i tryed to use FROM and INNER JOIN, but again, it does not worked... Any ideas if is possible to do something like this in SQL Mobile?
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
