I am executing an update query in access as follows....
UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = IIf([DtePrice1]<=CDate(Date()),[AmtPrice1],IIf([DtePrice2]<=CDate(Date()),[AmtPrice2],0))
WHERE (((tPriceRaw.IdPriceType)=8));
Here's my stab at converting this to a stored proc.
UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO)
SET tPriceSmart.AmtOverRide =
SELECT
CASE
WHEN DtePrice1 <= GetDate() THEN AmtPrice1
ELSE (CASE WHEN (CASE DtePrice2 WHEN '00/00/0000' THEN '1/1/1900' END) <=GetDate() THEN AmtPrice2 ELSE 0 END)
END
WHERE tPriceRaw.IdPriceType=8
I kknow the Join Statement works becasue it does work in the context of a select. I also know the case logic is working becasue I am using it in an Insert query elsewhere.
Can't quite pull it together though. I am getting an error at Line #1 "(". I gave it a good try though before I resorted to throwing it into the forum. I am sure it is a stupid syntax error. Any help would be appreciated.
Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.
I have two tables, 1) Ticket_Purchase, 2) Flight.
The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID
The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination
I am trying to create a query that will tell me: On which flight were the most first class tickets sold?
There are only two types of classes; 'E' for economy and 'F' for First Class.
So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?
The SQL I wrote for the above is:
Select Class_of_Ticket, Flight_Number From Ticket_Purchase Where Class_of_Ticket = ('F') Order By Flight_Number;
And it produces:
Class_of_Ticket Flight_Number --------------- ------------- F 1 F 1 F 1 F 2 F 2 F 3 F 3 F 3 F 3 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 4 F 5 F 5 F 6 F 6 F 6 F 7 F 7 F 8 F 8 F 8 F 9 F 9 F 9 F 9 F 9
(38 row(s) affected)
Rather I would like it to produce: First_Class_Seats Purchased Flight_Number --------------------------- ------------ 14 4
I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.
Thank you for any help you could offer. It would be much appreciated.
Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.
We're trying to set a new standard at my office, of using GUIDs as database ID fields - all new tables in all databases will have a GUID as the ID field, using the UniqueIdentifier data type.
The problem that we are running into, is that different applications interpret the UniqueIdentifier data type differently - ADO 2.x interprets it with the opening and closing { } braces. ADO.NET does not include the { and } in the GUID structure, SQL Server Query Analyzer does not include the { } braces, and SQL Server Enterprise Manage does include the { }
The problem with this is in two parts: 1) VB6 does not have a GUID structure or data type, so we are treating GUIDs as strings... but VB6 doesn't recognize "{012345678-abcd-ef01-2345-6789abcd}" and "012345678-abcd-ef01-2345-6789abcd" as the same string 2) when sending a URL link through an email, the { } braces break the link - in all email applications that we have tested. This include Groupwise 6, Groupwise 6.5, IPSwitch Web IMail, Outlook 2000, and Outlook Express (IE 6).
We're becoming very frustrated with the problems at hand, and need to know how others have worked around these problems. Please respond with any kind of advice or any real life situations where you have encountered this and found a solution, etc.
Two problems / questions I could do with some help on please......
1). How do you clear a spreadsheet before loading it ? I am sending a spreadsheet by Email on a regual basis. Each time I run my SSIS package the data is appended to that of the previous run. I need just the contents of this data load to populate the spreadsheet each time. I have tried various methods / techniques for achieveing this so I just need to know the accepted method. Sorry if this is very basic ( as it should be ?!) but I seem to be missing the point somewhere.
2). I have tried to resolve issue 1 above by having an empty 'template' spreadsheet and copying that one over the top of the previously populated one. I get an error saying that the source spreadsheet ..'is in use by another processes.' Amusingly, as a test, I can delete it but I cannot copy it !
Are there any decent workthroughs on working with destination excel speadhseets ? Im struggling.
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :o
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...
Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...
I know this may sound weird but that really is the case :eek:
Hello! I have an SSIS package that basically collects Excel files from an FTP server, deposits them to a file share on a SQL server and then processes each Excel file into a SQL 2005 database using the ForEach enumerator.
The package runs flawlessly when executed directly on the SQL server. The problem starts when I set this package to run via the SQL Agent. I have looked through these forums and other places and have done what this article suggested http://support.microsoft.com/kb/918760; changing my package protection to "Dont Save Sensitive" and setup the package to store its configuration in a SQL table. Despite all this I still continue to get this error when the package is run via the Agent:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelSourceFile" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
The Excel files are not password protected, no funky settings, nothing complicated, its just a plain Excel file with a few rows of data on one worksheet. The package transfers the files from the FTP server to the share on the SQL server with no problem, I can see that the files exist on the share, so it isnt a case of the Excel connection manager not finding any files to process.
The Agent account is running under administrator privileges and has full access to the share, so it shouldnt be a permissions issue either.
I have one report (R1) which includes a list of ports ("=Fields!Port_Name.Value")
Another report (R2) supplies details for individual reports and has a parameter named PortDWIDPortName which gets port names from the same dimension as those in R1. So in the field properties of the Port field in R1, I selected Navigation > Jump To Report and entered PortDWIDPortName = Fields!Port_Name.Value (also tried changing .Value to .Key or .UniqueName) but although it jumps to report R2 it does not enter a value for the parameter (ie leaves it blank or displays <Select Value> depending on whether multivalue or not).
Does anyone know the right syntax to pass the parameter ? If not, does anyone know to view what Jump To Parameter is returning so I can figure how to doctor it!
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username 2 FROM users 3 WHERE user_username LIKE '%[!-)]%';
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this: KeyField Value1 Value2 Value3 The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar) GO Update Provider_APC_2004_05 set EmAdmsCount12mths = (Select COUNT(*)-1 from Combined_Admissions where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or (Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND Combined_Admissions.AdmMethod like 'Emergency%')-- and -- CA.NHSorPrivate = 'NHS')) FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]); string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, "; updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(updateSQL, con); cmd.CommandType = CommandType.Text;
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e) { int areaId = 0;
if (Request.QueryString["doc_area_id"] != null) { areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]); }
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help. Thanks!! UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
OK, so I have this page where a user can edit multiple fields in a database record. My problem is, that once changes have been made and the user hits the "save" button, everything SEEMS to run just fine (no errors, other things that should happen when the button is clicked happen normally), but nothing is changed. It's almost like the update function (which was created with Microsoft ASP.NET Web Matrix) is working, but it's being fed the old values for the page, rather than the updated ones. Any ideas on what might be causing this? It's not like anything I've seen before. Thanks Kaiti
I gave a storedprocedure for gatting data into gridview,just go through that. Select Ind.Industry_Name,Com.Company_Address from Pcra_Industry Ind join Pcra_Company Com on Ind.Ind_ID_PK=Com.Ind_ID_FK Here iam getting two fields from two different table depending on the Id. So how to write a Query to change the fields in the different tables. please Help me...
Im having a problem when using my update statement on button click. I want the field to be updated to be true whenever the button was clicked but it doesn't seem to like my current syntax. Can someone point me in the right direction please? I know its really simple but when you have just been looking at something for so long you can never see it!!! Thanks SqlCommand myCommand = new SqlCommand("Update Notice SET Resolute = '" + Resolute + "', DateClosed= '" + DateClosed + "', Resolved = 'True' WHERE NoticeID = " + NoticeID + "", myConnection);
Hi Guys I have a problem with UPDATE query. Here is my Select query. SELECT d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDWhere d.EID ='1001' According to this query I have to update another query.This is my original Update Query. "Update Data_EMP set EStatus=0 where EID='" & TempEID & "'" Now i wrote update query like this UPDATE d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDSet set EStatus=0 where EID='" & TempEID & "'" Shall i write Update query like this and Is there any issues in this update query can any one let me know? Thanks
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario. I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress FROM account.Customer a Join account.Email b ON a.customerid = b.customerid WHERE lastname in ( SELECT lastname FROM account.Customer c Join account.Email d ON c.customerid = d.customerid GROUP BY lastname, firstname, emailaddress HAVING count(*) >1 AND firstname = a.firstname AND emailaddress = b.emailaddress ) ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
I have an update query which updates a field if the contents of 3 other fields in the table matches the result of a Where clause. Is it possible to only update the first matched row in the table rather than all rows that match the Where clause. Forgive if this is simple but I am a newbie to SQL 7. Regards Andrew Wall
I have the following table: AreaName Population RankNumber ======== ========== ========== Annerley 200 NULL Balmoral 50 NULL Chermside 350 NULL
Is it possible the write an update query that, after ordering the table by Population, it sets RankNumber to 1 for the first row, sets RankNumber to 2 for the second row, and so on??
The resultant table would look like: AreaName Population RankNumber ======== ========== ========== Balmoral 50 1 Annerley 200 2 Chermside 350 3
I want to update the code so that all records with the same category has the same code. It would also start with 1. The end result would be the following:
CATEGORY ACCT CODE
C ABC 1 C XYZ 1 A EFG 2 A PQR 2 A LMN 2 B STU 3 D IJK 4
Maybe some one can help me... I have 2 tables with the following structure: CREATE TABLE [table1]( [GRID] [int] IDENTITY (1, 1) NOT NULL , [GID] [varchar] (10) NOT NULL , [RID] [int] NOT NULL ) On primary
CREATE TABLE [table2] ( [RID] [int] IDENTITY (1, 1) NOT NULL , [RText] [varchar] (400) NULL ) ON [PRIMARY]
If [RID] in table 1 exists more then one time (the first occurence leave as is) and then create a new [RID] every time it is found in table2 with the RText from [RID] and update table1 with applicable [RID] .
I have a query i need to write that will effect over 12,000 rows of data so i can't get it wrong! Can anyone give me some advice i can update individual records using ASP and uniqueid's but this query need to be done on SQL Exp browser.
What i have is 2 columns one called fxactivitystatus and one called activitystatus i need to copy all the fxactivitystatus fields to the activitystatus fields where the activitystatus are blank.
HI Guys Wonderful to see you again and of course I have a question for ya.. I have a query that finds matching records in two tables one is the Conditional Table the other is the Termination table. What I would like to do is update the records it finds, which is basically a yes/no field. It bascially says if a terminated employee was once on a conditional they the field will say yes. and I need to update that field to yes. This query produces approximately 150 records that the Termination.tbl and the ConditionalLicense_View have in common and I need to change those 150 records Yes/No to a Yes?? does that make sense??
SELECT ConditionalLicense_View.[TM #], ConditionalLicense_View.FirstName, ConditionalLicense_View.LastName FROM ConditionalLicense_View LEFT JOIN TERMINATION ON ConditionalLicense_View.[SS#] = TERMINATION.SocialSecurityNumber WHERE (((TERMINATION.SocialSecurityNumber) Is not Null));
table2 is the latest table with updates, some of the fields in the extension field are nulls. i want to update table1.extension to table2.phonenumber based on the primary key directoryid
update table1 set table1.extension = table2.phonenumber INNER JOIN table2 on table1.directoryid = table2.directoryid where table1.extension = '' or table1.extension is NULL.
