HiYou can do a select top 1 * from <table>, my question ishow does one delete the row whithout having to do lots of whereclause ?Is there a equivelant delete top 1 from <table>Thanks
Would some one please help me with the syntex on how to run "restore filelistonly" or restore verifyonly" on a SQL backup which has multiple filesets?? My backups locations are as follow: RESTORE VERIFYONLY From disk = 'E:syndicated_databank__bkup_01.bak', 'E:syndicated_databank__bkup_02.bak', €˜E:syndicated_databank__bkup_03.bak€™, €˜E:syndicated_databank__bkup_04.bak€™, €˜E:syndicated_databank__bkup_05.bak€™
I tried to do a restore with the above, I got error The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.
update xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit: Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit: Could it be to do with this linked server setting? Collation Compatible right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
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
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
hi need help how to send an email from database mail on row update from stored PROCEDURE multi update but i need to send a personal email evry employee get an email on row update like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
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
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown. The C# code: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn); cmd.CommandType = CommandType.StoredProcedure; Label accomIdLabel = (Label)DetailsView1.FindControl("lblID"); int accomId = Convert.ToInt32(accomIdLabel.Text); cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId; cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath(); try { conn.Open(); cmd.ExecuteNonQuery(); } catch(Exception ex) { throw ex; } finally { conn.Close(); } The Stored Procedure: ALTER PROCEDURE developers.fort_SaveRedirectURL ( @accomId int, @path varchar(250) ) AS DECLARE @enabled bit, @oldpath varchar(250)
/* Ensure that the accommodation has been enabled */ SELECT @enabled = enabled FROM Experimental_Accommodation WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1) BEGIN /* Now check if a path already exists */ SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL BEGIN /* If Path already exists then we should keep the existing URL */ /* Otherwise, we need to insert a new one */ INSERT INTO Experimental_Adpages_Redirect (oldpath, accom_id) VALUES (@path,@accomId) END END RETURN
I'm new to this forum. This 'problem' has occured many times, but I've always found a way around it. I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values. Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank? Thank you very much.
I do realize that his could be posted in a few spots but I think the answer is in the SQL. I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting. ex: <asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox> I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together. I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters. Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update. There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it. wbochar
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below: btnUpate_Click(object sender, EventArgs e){ sqlDataSource1.Update(); sqlDateSource2.Update();} But, the records haven't updated. In SqlDataSource2_Updating() function, I found all the parameters is null. So, how to modify my code to do it.
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?
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.  Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
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?
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 '%[!-)]%';
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
OK, This is an Update that I have working, But what do I do, if the customer does not exist already it doesn't add the customer? How should I remedy this? if the customer does exist works great.
UPDATE AC SET CustId = Left (CustomerId,10), CustName = Left (CustomerName,25), Addr1 = Left (Address1,25), Addr2 = Left (Address2,25), City = Left (ca.City,15), Region = Left (State,2), PostalCode = Left (Zip,5) FROM RIO.dbo.tblArCust AC INNER JOIN (SELECT CustomerCode, MAX(LastUpdatedDate) MaxDate FROM COFFEE.dbo.vueCustomerAddress GROUP BY CustomerCode) V ON V.CustomerCode = AC.CustId INNER JOIN COFFEE.dbo.vueCustomerAddress CA ON CA.CustomerCode=V.CustomerCode AND MaxDate=LastUpdatedDate WHERE CA.addresstypeid = 1
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.
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat) begin update med set REC_FLAG = 2 from deleted dt where med.uniq_id = dt.uniq_id and dt.pstat = 2 and dt.spec_flag = 'kop' end
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA] ON [dbo].[ProdDesc] AFTER UPDATE AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation) UPDATE p sET p.ModifiedDate = GETDATE() FROM ProdDesc AS p WHERE p.ID = (SELECT ID FROM inserted)
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!
i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.
i want to avoid the trigger launching for the last column (UpdatedOn).
how can i detect the rows that changed, and modify only the update date/time? i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.
another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
