Update 2 Tables In Single Statement
Is it possible to update 2 tables in a single t-sql statement?
If Yes, whats the syntax?
Update Table1,Table2 ... is not working
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Can A Single Insert Statement Will Update Multiple Tables
Hai Guys: I have a situation where i need to insert a value called StudentID to several tables with one insert statement. Can you please inform me the SQL statement to acheive the above mentioned task. (by the way iam using SQL Server 2000, i dont need triggers / procedures as they are too tricky and hard to use). Thanks / Regards, Dotnet Geek.
View Replies !
View Related
Single Statement For Insert Or Update
In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net? My old VB6 code Dim dbData As New ADODB.Connection Dim rs1 As New ADODB.Recordset Dim strParm As String Dim strCusNo As String ' strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password" dbData.Open strParm ' strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'" rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText If rs1.BOF And rs1.EOF Then rs1.AddNew Else End If With rs1 !CusNo = strCusNo .Update End With rs1.Close ' Set rs1 = Nothing dbData.Close Set dbData = Nothing Is there an ADO.Net equivalent? thanks,
View Replies !
View Related
Single Statement To Delete Record Into More Tables
Hi , I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time? For example if I've two tables involved by join DELETE <...> from Customers A INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID I Must use two statement to remove records from both the tables? Thx
View Replies !
View Related
Update Multiple Varbinary Records With Single Sql Statement
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ... What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx '2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn) '3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername) Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd) 'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
View Replies !
View Related
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
View Related
Update Multiple Tables In Single Query
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 Replies !
View Related
Can We Insert/Update Into Related Tables In A Single Round Trip ?
I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ? Thanks Anurag Agarwal
View Replies !
View Related
UPDATE Statement Involving Tables In Different Databases
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
View Replies !
View Related
SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane... (0 row(s) affected) (1 row(s) affected) How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect) (1 row(s) affected) If anyone has any ideas where to look next, I'd appreciate it. Thanks
View Replies !
View Related
Plz Help...update Value In Multiple Db Table Using Single 'update Command'
hi,friends we show record from multiple table using single 'selectcommand'. like.... --------- select * from cust_detail,vend_detail --------- i want to insert value in multiple database table(more than one) using single 'insert command'. is it possible? give any idea or solution. i want to update value in multiple database table(more than one) using single 'update command' i want to delete value in multiple database table(more than one) using singl 'delete command' it is possible? give any idea or solution. it's urgent. thanks in advance.
View Replies !
View Related
Need A Single Sql Statement
How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second?? e.g. How can I left join these two queries with the joinfield1,joinfield2 fields?? 1st query Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value 2nd query Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4 where agfield5=Value Group By joinfield1,joinfield2
View Replies !
View Related
Is It Possible In Single SQL Statement
Does anyone know how should I write the sql for getting the following result? Original Table like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 3 06/12/02 2 06/12/02 3 06/12/03 2 06/12/03 3 ------------------------------- Curernt SQL: When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row. Example, SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2 ------------------------------- [WorkDay][AgentCode] 06/12/02 2 06/12/03 2 ------------------------------- I would like to know the agent is in the specified date. The expected result like below. ------------------------------- [WorkDay][AgentCode] 06/12/01 NULL 06/12/02 2 06/12/03 2 ------------------------------- Please help its urgent
View Replies !
View Related
Single Select Statement
Hi, i have an input parameter @PageloadYN Bit Null if @PageloadYN = 1 then Select top 500 Records from the Table if @PageloadYN = 0 then Select * from the Table i tried like this Select top 500.* From Table where @PageloadYN = 1 Select * From Table where @PageloadYN = 0 Is there any way to get details in Single select statement only.
View Replies !
View Related
Single SQL Statement Solution….
What is the single SQL statement to truncate the blank space on either side of data. Ex. Table1 has Name as column. I have records filled with blank space on both side for Name field. With one query I want to correct (truncate the leading and trailing space) the data. How? SQL Server 2005 SP2. Thank you, Smith
View Replies !
View Related
UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View Replies !
View Related
Help With Creating SQL Statement To Get Data From Single Table...
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table: I have the following table of data __User___Votes___Month __A_______14______2__A_______12______3__A_______17______4__A_______11______5 __B_______19______2__B_______12______3__B_______15______4 I want to beable to pull out the total number of votes a user has had over a period of months. eg Total up each users users votes for months 4 and 5 that would give: __User____TotalVotes ___A________28___B________15 An added complecation is that user B does not have any data for month 5 Any help or pointers would be fanstatic Many thanks
View Replies !
View Related
To Alter Multiple Column With Single Statement
It is possible to alter multiple columns within a single alter table statement? I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement. http://www.blogcoward.com/archive/2005/05/09/234.aspx[^] Does anyone know about that? Thanks, Mushq
View Replies !
View Related
Can Someone Clarify Why Only A Single-statement Can Be Executed In A Command?
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions. I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time? For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
View Replies !
View Related
Single Update Query
Table Name:-emp table structure:- Name Gender A Male B Male C FeMale D FeMale I want only one update query to update this table like this table structure:- Name Gender A FeMale B FeMale C Male D Male please help me?
View Replies !
View Related
How To Update Single Column
hi guys i have a problem;;; i have to update table''' querey is--TABLE1 HAS A COLUMN CALLED CITY .THE RECORDS WITH CITY='DELHI' ARE IS TO BE UPDATED WITH CITY='MUMBAI' AND THE RECORDS WITH CITY='MUMBAI' ARE IS TO BE UPDATED WITH CITY='DELHI' ... i HAVE TO WRITE A SINGLE SQLSTATEMENT TO DO THIS NOT WITH THE HELP OF CURSOR
View Replies !
View Related
Multiple SET/WHERE Within Single UPDATE?
Can an Update statement support multiple SET/WHERE clauses? The following example won't work, but this is what I'd like to be able to do: Code Snippet UPDATE [CPSAgacar].tmp_CTA2 SET [Admit Date] = NULL WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL SET CalcDischDate = Discharge WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL AND CPSAgacar].tmp_CTA2.CalcDischDate IS NULL Thanks.
View Replies !
View Related
Single SQL Update Statements
What is the single SQL statement to truncate the blank space on either side of data. Ex. Table1 has Name as column. I have records filled with blank space on both side for Name field. With one query I want to correct (truncate the leading and trailing space) the data. How? SQL Server 2005 SP2. Thank you, Smith
View Replies !
View Related
Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Hello, I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working. I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
View Replies !
View Related
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are: 1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup. 1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types. 1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately. I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that. Thanks in advance for any help/suggestions, Bob
View Replies !
View Related
How To Identify Special Characters With A Single Case Statement
hi All :I have an insert statement which readsINSERT Into TableA (Col1)SELECT CASE WHEN Col1 LIKE '%[a-z]%' THEN 999999 ELSE Col1 END ASCol1,FROM TableB**********************TableA. Col1 has a datatype of Int.TableB. Col1 has a datatype of varchar.My insert is failing as it is trying to insert special character,identifying a in the input columnHow can i write a similar case statement to filter out specialcharacters and replace with 99999Also can you refer me to a place where i can have a list of specialcharacters and if need be write mulitple case statements to filterthem out.thanksRS
View Replies !
View Related
Single Query To Update And Append
I need to write a single query that will append the values from one table into another table but I need to update a single field in the table with a static value. What I'm working with is an Access .adp with an SQL 2000 backend. The database is used to track ticket sales, payments, and contact info for season ticket holders. Prior to upsizing, each year the database would be copied and then choice bits modified for the next year. In other words, the history was in several databases, i.e. db2001, db2002, db2003 and for the current year dbcurrent. I decided to create a historic tables in the current DB rather than have umpteen DBs on my SQL server. My problem is that I need to create a query that appends to my table the previous years data and then updates the season field to reflect the season that the data came from. In short, say I have a table named accounts with fields account, customer, addr1, addr2, ..., ticket type and I want it to end up in account_hist with fields account, customer, addr1, addr2, ..., ticket type, SEASON and make the season equal say 8 which represents the 8th season the team has played. I can get both queries to work seperately :D , but for end user ease, I want to perform both actions at the same time :confused: . Can anybody point me in the right direction? Thanks
View Replies !
View Related
Single Update Query Using In Clause In SP
Hello, I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value). I'm storing id with comma (,) separator. Below is my asp.net code: string sId=string.Empty; for(int a=0;a<Id.Count;a++) { Id += Id; if(a!=Id.Count-1) sId +=","; } In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar. Plz help me create Stored Procedure in SQL 2005 based on above scenario. Thanks
View Replies !
View Related
How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message. FROM | SUBJECT | User Photo | || | Subject || User Name | | Here is the list of the relevant 4 tables of my database and the relevant fields..... aspnet_Users tableUserId (used to link to Member table)UserName Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table) Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive) Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max)) So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far..... SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender. And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol. This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
View Replies !
View Related
Update Multiple Records From A Single ASP.NET Webpage
Got a beginner question here... Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data). Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004? This would be strictly for updating information. I would have a separate form for adding a new entry. Thanks for your help.
View Replies !
View Related
UPDATE A Variable To Different Categories By A Single Step?
Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end
View Replies !
View Related
Insert And Update A Table In Single Web Page
A problem has come up in designing a Web page to maintain a small reference table in SQL Server 2000 (9 columns, about 25 records). I tried to design the Web page to allow INSERT and UPDATE operations using a single page that posts back to itself. The page contains a set of empty form fields for a new record, followed by a set of filled-in form field for each row in the table. The form fields for existing records are given a unique name based on the field name concatenated with the primary key value for that row. If I set up the page to INSERT only, it works properly. But when I add the fields for existing records, the INSERT operation malfunctions. Specifically, anytime a set of existing fields for a particular column is added to the page, the INSERT will no longer work properly for that column. This is true for all fields except the primary key field. It always INSERTs correctly. I tried adding only some columns to the set of existing form fields. In that case, the INSERT operation added the correct values for the fields that were not listed in the existing records section, but failed for the others. I am using the INSERT INTO syntax for that operation and the recordset .Update syntax for the edits. I tried using the recordset .AddNew/.Update syntax for the insert, but it exhibited the same problems. The column data types contain smallint, bit, nvarchar, and ntext types. I know that the correct values are being put into the INSERT statement. I also tried renaming the INSERT form fields to be totally different than the names of the existing record fields. But the problem comes back no matter what. If necessary, I can split the logic so that inserts and updates are handled by different pages. But I would like to make this work if possible. If a reader knows why SQL Server is causing this problem, any help would be greatly appreciated.
View Replies !
View Related
Update On Single Row Fails - Timeout Expired
I get a timeout expired error when running the following in a vb component. 'update cm_event_notification Set processed = '3/29/01 9:46:01 AM' Where dn = '{2E738808-23B4-11D5-B49A-00508BD934F8}' The table dn column is a primary key, when I run it from sql Anyalzer it works. If there are two rows to update it works, just the single row update times out! Thanks in Advance, Phillip M. Tricoli
View Replies !
View Related
Update Multiple Rows In A Single Query?
I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order. Code: update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111 PHP Code: $qt="update categories set "; for($i=0;$i<$num;$i++){ $a=$i+1; $qt.="sortorder=$i"; if($a<$num){ $qt.=", "; } } $qt.=" where userid=111"; Using PHP I can amend the loop above to slot in a row I want so I can change the sort order. unfortunately I'm not sure how to build such a query in mssql, can anyone help?
View Replies !
View Related
Trigger To Update One Record On Update Of All The Tables Of Database
hi! I have a big problem. If anyone can help. I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time. I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database. But i don't know exactly how to do the coding for this? Is there any other way to do this? can DBCC help to retrieve this info? Please advise me how to do this. Thanks in advance. Vaibhav
View Replies !
View Related
Update If Exist-one Table To Another From Tow Date Fields- Single Row For Each Day
question need help how can i use this code below not for insert i need it for update another table but only if exist the link to the code in this FORUM http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True Adam Haines wrote: GPS, Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic. Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html. Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt. Now the code to get the results you need: Code Snippet declare @t table( id int, fname char(4), Start_Date datetime, End_Date datetime, val_holiday int ) insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 ) insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3) insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4) insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 ) select id, fname, cal.ISODate, val_holiday from @t t1 inner join Calendar cal on cal.isodate >= t1.start_date and cal.ISODate <= t1.end_date is possible to do it TNX
View Replies !
View Related
Limit Unknown Update Command To Single Table
Hi I have a piece of software that accepts sql commands as user inputs - typically this would be 'update table set xxx where' or 'delete from table where'. I would like to know if it's possible (apart from security settings) to ensure that any valid command entered by a user will only affect one table - that is the target table - and that nothing else on the database will be available or affected by the update. Thanks in advance.
View Replies !
View Related
How To Retrieve Two Columns From Different Tables As A Single Result Set
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this: 1. SELECT name FROM table1 2. SELECT name FROM table2 3. Join the two results together and order them alphabetically 4. Return the result set I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
View Replies !
View Related
|
TRACKER
