I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes
Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
I have some code (C#) that runs an SQL update query that sets thevalue of a column to what the user passes. So, this causes an errorwhen anything the user passes in has a ' character in it. I'm surethere's other characters that'll break it too. So, I was wondering,how do I get around this? Is there some commonly accepted regexpattern that will make the value safe to run in an SQL query? How canI take care of any values that need to be escaped?I'm not using any fancy ado.net objects:string sql= [whatever the user passes in]SqlConnection connection = newSqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());connection.Open();SqlCommand command = connection.CreateCommand();command.CommandType = CommandType.Text;command.CommandText = sql;try{int result = command.ExecuteNonQuery();if (result != 1){Response.StatusCode = 500;Response.Write("The file has been uploaded, but wecould not update the DB");Response.End();}}catch (InvalidOperationException){Response.Clear();Response.Write("error");Response.StatusCode = 500;Response.End();}connection.Close();
I'm a bit of a newby to creating update queries so I was hoping those moreexperienced could help with what should be a simple query. I do know tocreate backups and test on a test database first.I need to copy an items' cost from location 'CA' to the cost in location'OH' when the cost in 'CA' is not zeroTable name is ITEMSLocation column is LOCCost column is COSTHere's the Select statement from Enterprise Manager's Open Table, Query toolSELECT loc AS Expr1, cost AS Expr2FROM ITEMSWHERE (loc = 'CA') AND (cost <> 0)I just can't seem to get this into a working Update query.Thanks in advance...
SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact
Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side
For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact
Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table
in my original database have a column which is for "path" ,the record in this column is like → �mms://192.12.34.56/2/1/kbe-1a1.wmv】 this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..
I have to change � mms://192.12.34.56/2/1/kbe-1a1.wav】 to � mms://202.11.34.56/2/1/kbe-1a1.wav】 I tried to find the reference book and internet . can't find out the answer for this problem. can you help? or maybe is it a impossible job? thanks
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master alter database xxx set read_write with rollback immediate alter database xxx set single_user with rollback immediate
use xxx update tablexxx set columnxxx=yyy use master alter database xxx set read_only with rollback immediate alter database xxx set multi_user with rollback immediate
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
Msg 209, Level 16, State 1, Line 8 Ambiguous column name 'PartNrFabrikant'. Msg 209, Level 16, State 1, Line 8 Ambiguous column name 'omschrijving'. Msg 209, Level 16, State 1, Line 8 Ambiguous column name 'verkoopprijs'. Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title from H..Import_Table returns 'PRN - Concord' 'PRN - San Jose' 'SLP - PRN - San Jose' 'San Jose - PT - PRN'
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
i someone had teach me how to write a query in datatable. however i need to get the data out from my database rather than the data table. can someone teach me how should i do it?esp at the first like.... like DataTable dt = GetFilledTable() since i already have set of data in my preset table i should be getting data from SqlDataSource1 right ( however i am writing this in my background code or within <script></script> so can anyone help me? protected void lnkRadius_Click(object sender, EventArgs e) { DataTable dt = GetFilledTable(); double radius = Convert.ToDouble(txtRadius.Text); decimal checkX = (decimal)dt.Rows[0]["Latitude"]; decimal checkY = (decimal)dt.Rows[0]["Longitude"]; // expect dt[0] to pass - as this is our check point // We use for rather than fopreach because the later does not allow DELETE during loop execution for(int index=0; index < dt.Rows.Count; index++) { DataRow dr = dt.Rows[index]; decimal testX = (decimal)dr["Latitude"]; decimal testY = (decimal)dr["Longitude"]; double testXzeroed = Convert.ToDouble(testX -= checkX); double testYzeroed = Convert.ToDouble(testY -= checkY); double distance = Math.Sqrt((testXzeroed * testXzeroed) + (testYzeroed * testYzeroed)); // mark for delete (not allowed in a foreach - so we use "for") if (distance > radius) dr.Delete(); } // accept deletes dt.AcceptChanges(); GridView1.DataSource = dt.DefaultView; GridView1.DataBind(); }
Hello, I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.
I have about 8000 records close to 2000 records for the last 4 years and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.
Here are my field names: [year] ***4 choices 2007, 2006, 2005, 2004*** [name] [rush_no] ***integer*** [rush_net] ***integer*** [YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***
I also need to create the same table that will "total/sum" the same records.
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock] AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock FROM tab_productdetails INNER JOIN tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
I 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
My goal is to split the full contact name into first and last as well. Because that is what is in the database and I want to keep it consistent for the few thousand records missing it. Do I update this using a stored Procedure? How would it be formed? CREATE PROCEDURE dbo.ap_Insert_FirstLastName_WhereNull SELECT * FROM Booth_Visitors_Data WHERE First_Name IS NULL And Last_Name Is NULL And Contact_Name IS NOT NULLORDER BY Contact_Name Desc Update Booth_Visitors_Data Set ?? Or is this impossible with a stored procedure? SHould I wirte a .aspx page to do it? Or can I do it all in code-behind??Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim con As New SqlClient.SqlConnection con.ConnectionString = "Data Source=10.10.10.10;Initial Catalog=Leads;Persist Security Info=True;User ID=xxxxx;Password=xxxxx"Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "SELECT * FROM(Booth_Visitors_Data) WHERE(First_Name Is NULL And Last_Name Is NULL And Contact_Name Is Not NULL) ORDER BY Contact_Name Desc" myCommand.BeginExecuteReader() myCommand.Connection = con con.Open() con.Close() End Sub If I can do it in code-behind, how does it look in modern .net code as opposed to the old classic asp way? [Edited by Dinakar Nethi] Masked userid/pwd in connection string. [/Edit]
I am a SQL Server Express and VS 2005 rookie. I currently have a form with a datagrid on it, mapped to a binding source with everything basically done for me using a wizard. I want it so that the user can enter data into the grid, and the data is stored in the database. However, everytime i click save, it doesn't actually save the data, so that everytime you execute the application in debug again, the data is gone. I have the End Edit and Update statements correct. What is wrong!
Hello all,I'm new in this newsgroup and I apologise if my question has already done.Let me knwo if FAQ is avaible somewhere.I use msSQL server to manage data for a B2B portal.Orginal data comes from as400 db2.I need to update msSQL data from db2 source.I used MetaDataService executed by SQL server Agent, but dosn't work asexcepted 'cause I need to delete all row in a table and then copy all newrows, this works fine but what happen if an user is quering data while sqlagent starts?I need to make data update very often so I need other solution.The update procedure must follow these rules:1- check diffrents between a table in DB2(source) and the same table onmsSQL server2- update my msSQL table if some diffrents was found.I'm a beginner with msSQL server, any advices are welcomed, I need a startinpoint.please help.thank you allkindly regardsAlberto
I have been trying to think of a solution to a problem i have and still not sure how to go about it.
I have a database filled with message data that is sent by one user to another.What i am trying to do is have it so (if users are online) they are notified - by a popup or something - that a new message has arrived for them.My idea was to create a trigger on the message table to update a change table with sender and recipient ids and date of change in it.Not a problem so far but what is troubling me is that i want the recipient to automatically query this change table every say 30 seconds to check if new message has arrived with their id.Tryed using ajax update panel and timer but naturally that kept posting back whole page - looks really terriable :( - tryed sqlnotification but as far as i can see that is only changing cached data for current data - not the same issue i think.Is there a way to query db table regualry without the client side knowing? - thinking along lines of how outlook requeries server for new messages and displays popup to say new message but doesnt interupt you if doing something else.Any help would be greatly appreaciated.Thanks
Hi Everyone, I develop a asp.net 2.0 site with Sql2005 Database, now i want to publish it on a webhosting service. Once I copy the DB to app_data folder on webhosting, what i can do to update data from my local sql2000 server? I want update data daily, may I use SQL replication? How? I can access my sql2005 DB by FTP? Any ideas? Thanks a lot. Regards, Bordonhos
I am currently wrapping up a website upgrade for a client and I am working on a development server/database. The development server/database will become the live version. When the upgrade goes live, I will need to update that database with the latest data from specific datatables (no all of them) in the previously live database, but I don't know how to do a bulk refresh of datatables. Problem: specific datatables (not all datatables) from Database1 need to be updated with the data from Database2. Database1 and Database2 are copies of each other with vast differences in some of the data. Result: All of the current, up-to-date data needs to reside on Database1. Solution: Any ideas? I am using MSSQL 2000 and the databases reside on the same server.
I am attempting to update contact data that is displayed in a form. Unfortunately the followiing code that runs off the button click event does not seem to work or I am misssing something to make it fire. I get no errors but the data does not get updated. What am I missing? Thanks.Private Sub btn_Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Submit.Click Dim strConnection As StringDim cn As SqlConnection Dim sql As String strConnection = ConfigurationSettings.AppSettings("ConnectionString")cn = New SqlConnection(strConnection) sql = "UPDATE vw_BS_IssuerContacts SET ContactLastName = @ContactLastName, ContactFirstName = @ContactFirstName, ContactTitle = @ContactTitle, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, Country = @Country, ZipCode = @zipCode, AccessLevel = @AccessLevel WHERE IssuerCode = @IssuerCode"Dim UpdateCommand As New SqlCommand(sql, cn) UpdateCommand.Connection = cn UpdateCommand.CommandText = sqlUpdateCommand.Parameters.Add(New SqlParameter("@IssuerCode", Lbl_IssuerCode.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@ContactLastName", txt_Lname.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactFirstName", txt_Fname.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@ContactTitle", txt_title.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactEmail", txt_Email.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@ContactPhone", txt_Phone.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address1", txt_Address.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@Address2", txt_Address2.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address3", txt_City.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@Country", txt_Country.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ZipCode", txt_zipCode.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", Lbl_currAccessLevel.Text)) 'UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", SqlDbType.Char, 2).Value = txt_CurrAccessLevelSelectedValue))UpdateCommand.Parameters.Add(New SqlParameter("@UserName", txt_Username.Text)) UpdateCommand.Parameters.Add(New SqlParameter("@infopw", txt_pw.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Company", txt_IssName.Text)) 'do the update cn.Open() UpdateCommand.ExecuteNonQuery()
I develop ASP.NET applications and use MS SQL 2000 Enterprise.
Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases.
Supposed there are 10 DBs. I want the data in those 10 databases updating to the central database directly. So, the gateway can gather all data of 10 DBs from a central DB.
Now, my question is how should I do it in MS SQL 2000? Can I do it with a stored procedure (SP)? If so, how does the SP like?
Or, SP can only perform on it's own DB, but not execute across DBs? If so, how can I do that?
Hi! I have quite strange problem, that I haven't seen before. When I use update command: UPDATE categorys SET banner_valid= '0', section_id= '1', main_cat_default= '0', banner= '', b_link= '', external_text= 'NÄ?kotnes parks ', in_frontpage= '1', name = '100. pants' WHERE (id = 130) Then the field EXTERNAL_TEXT should have value NÄ?kotnes parks but instead of this it makes it Nakotnes parks. I changed collation to Latvian and this did not work. But!!! When I open Enterprise manager and just type in NÄ?kotnes parks and save it then it is ok, but it does not work with Update/add script Any help or ideas???
I'm trying to update a single record in a table using data from a single record in another table. For the life of me I can't figure this out. I'm trying to avoid having to write a bunch of +=" string stuff in C#. Is there any way to do this with just SQL?Thanks in advance.
