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.
The statement has been terminated.
View 4 Replies
ADVERTISEMENT
Mar 19, 2008
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?
Thanks
Bob
View 3 Replies
View Related
Dec 29, 2007
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
View 3 Replies
View Related
Sep 21, 2006
Hello,
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
;
View 5 Replies
View Related
Oct 30, 2006
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
any ideas?
View 4 Replies
View Related
Oct 6, 2006
Hi People,
I am having a table which has some 10 cols, only one column had all Nulls. DB-SQL2K5
I am now writing a query like
Update Test1
set Id =
(Select t2.Id from
Test2 t2, Test1 t1
where
t2.Name = t1.Name)
as likely this query is faling as the sub query is retuning more than a row. What is the best method to achive my requirement?
Thanks
View 7 Replies
View Related
Jan 18, 2008
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!
exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.
View 1 Replies
View Related
Dec 11, 2007
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?
View 7 Replies
View Related
May 29, 2008
Hello,
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 ?
Thanks: Peter
View 5 Replies
View Related
May 2, 2008
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.
Thanks in advance.
View 4 Replies
View Related
Feb 21, 2007
I have a table with multiple records.
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.
I can not figure this out. :eek:
Any help would be GREATLY appreciated.
View 3 Replies
View Related
Feb 4, 2008
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?
Thank you for your help!
cdun2
View 5 Replies
View Related
Jan 3, 2006
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.
i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC
When the query runs, I want to see the following records:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
BUT NOT:
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC
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.
Anyhelp would be appreciated...
View 5 Replies
View Related
Jan 13, 2006
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??? :)
View 2 Replies
View Related
Jul 20, 2005
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.
View 3 Replies
View Related
Jan 3, 2006
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...
View 2 Replies
View Related
Mar 26, 2014
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.
View 20 Replies
View Related
Nov 30, 2005
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.
View 4 Replies
View Related
Oct 19, 2005
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!
View 1 Replies
View Related
Jun 14, 2008
hi friends
in my sqlserver 2005 i want to update two tables in a single query.
is it possible? or is it possible by creating a view?
thanks in advance.
shweta
View 3 Replies
View Related
Jul 23, 2005
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!
View 4 Replies
View Related
Jun 17, 2007
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
View 2 Replies
View Related
Aug 28, 2006
I have a simple update statement that set sthe student's password to their id number:
update studemo set passwd = ident
ident is not part of the primary key but there is a unique, non-clustered index using that column
This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?
View 10 Replies
View Related
Apr 3, 2008
Hello All,
I want to update multiple tables using single query and fields name are same of tables.
I am trying like:
update tablename1 t1,tablename2 t2 set t1.fieldname1 = t2.fieldname1 = 'value' where condition;
or
update tablename1 t1,tablename2 t2 set t1.fieldname1 = 'value' t2.fieldname1 = value where condition;
Plzzzzzz help me.Thanx in advance.
Thanx & Regards,
Smita.
View 17 Replies
View Related
Sep 30, 2013
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')
View 4 Replies
View Related
Nov 6, 2007
Hi,
I am using SQl server 2005.
want to update rows in 2 tables,which have a relation on Id field.
Some thing like
Update tblA a , tblB b
Set a.UpdatedDt=getdate(),b.Updateddt=getdate()
where a.Id=b.Id and a.Name='xyz'
can anyone out there help me?
Thanks
Renu
View 2 Replies
View Related
Feb 26, 2008
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!
View 5 Replies
View Related
Apr 17, 2008
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.
-El Salsero
View 5 Replies
View Related
May 28, 2007
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
Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)
Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)
Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)
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.
Any help, would be appreciated.
Thanks
View 3 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
May 18, 2004
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
Can someone shed some light?
TIA
View 1 Replies
View Related
Jan 31, 2007
Im trying to run this query:
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?
Thx
View 5 Replies
View Related
Nov 12, 2006
Hi all,
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!!)
Thanks all.
Chris
View 7 Replies
View Related
