UPDATE
Table_1
SET
Field0 =
CASE
WHEN @Field0 IS NULL THEN Field0
ELSE @Field0
END,
Field1 =
CASE
WHEN @Field1 IS NULL THEN Field1
ELSE @Field1
END,
... ...
Field9 =
CASE
WHEN @Field9 IS NULL THEN Field9
ELSE @Field9
END
WHERE
id = @id
Method 2
IF @Field0 IS NOT NULL
UPDATE
Table_1
SET
Field0 = @Field0
WHERE
id = @id
... ...
UPDATE
Table_1
SET
Field9 = @Field9
WHERE
id = @id
Method 3:
DECLARE @SQL nvarchar(max)
DECLARE @NeedComma bit
SET @SQL = 'UPDATE Table_1 SET '
SET @NeedComma = 0
IF @Field0 IS NOT NULL
BEGIN
IF @NeedComma = 1
SET @SQL = @SQL + ', '
SET @SQL = @SQL + 'Field0 = @Field0'
SET @NeedComma = 1
END
... ...
IF @Field9 IS NOT NULL
BEGIN
IF @NeedComma = 1
SET @SQL = @SQL + ', '
SET @SQL = @SQL + 'Field9 = @Field9'
SET @NeedComma = 1
END
SET @SQL = @SQL + ' WHERE id = @id'
EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
I am writing a new version of a task with and additional property, and wish to support upgrades between the versions. I have implemented the CanUpdate and Update methods, and update the Xml for the new property. The problem I then have it LoadFromXml gets called which promptly fails since it is still using the old version of the Xml. Have I misunderstood the point of the Update mechanism? It would seem to me that I��d better off ignoring Update and implementing the checks defaulting my new property within LoadXml. The new version of SaveToXml would sort out the upgrade when saved again.
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.What would be the more efficienct method to use?'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReaderDim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnectionmyItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"myItemCommand.Connection.Open()myItemReader = myItemCommand.ExecuteReader()Dim myfileout As StreamWritermyfileout = File.CreateText(Server.MapPath("out.txt"))myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")While myItemReader.ReadIf myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then'what method to to look up for example the category name using the category_quid referenced in the item master'Write out record for parts file
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
hi. i am working with PHP and MSSQL (i know its not a smart move, but I don't have a choice). I got almost everything working correctly except the date format.
I can select the date and display it the way I want by doing this select convert(VARCHAR(10), date, 105) as new_date FROM table.
But when I have a field to update the date I am having trouble.
How do I update the MSSQL database with a new date if the date displayed is DD-MM-YY
Dim ldDatetime As DateTime = Now() '11/12/2005 13:05:09 my command text is: "Insert Into WebSiteThread (TransactionDate) Values ( " & " ' " & ldDatetime & " ' " & ") " The result I got was "12/11/3091 13:05:09" Not "11/12/2005 13:05:09" What's wrong please!!
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
Here is the case. I have up and running database on MSSQL server. My client deliver me updates. Since now they were as INSERT statements, but they decided to change some stuff at their side. Now the updates will come as pairs of files (.dbf and .mdx) for each table...
Is there any way where I can add the MySQL Server as a Linked server to MS SQL server? Is there any way where I can update a table in MySQL server based because a row was updated in MS SQL server using a trigger?
Hi all,I have a problem here where I am trying to use SQL Parameters to update a column in the database, but the problem is that I need to concatenate the same column to some Text from an Application
This is the Code I Have, But it throws an exception
UPDATE rdm_comments SET commentinfo = commentinfo + 'additional info' where commentid=2356
I get an Error stating that i cannot use the addition operator for a string. Help
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert. Can some one Help me how can i do it.. THX Sorry for my bad englisht
I'll installing update http://support.microsoft.com/?kbid=899761 to correct memory usage. When installation process started I recieved this error - "An error in updating your system is occured." and installation failed. What I must doing to correct this issue?
Hi ~ I made simple stored procedure that is to update user information following as... ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) ) AS UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message. What's the problem ?
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
Hi I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database. Please Suggest Thanks in Advance.
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
Hi anyone can tell me how to create a table from an existing table in MSSQL. In oracle we issue " create table <tablea> as select * from <tableb>; " ? Thanks in Advance.
Hi there, Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
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 have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber Product OrderQuantity--------------- --------------- ----------------------0608 Prod1 30608 Prod2 120608 Prod3 2 What I am after is for the results to display something like this.OrderNumber Prod1 Prod2 Prod3 --------------- --------- --------- --------- 0608 3 12 2 This is using SQL Server ver 8.0
I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.
Currently, I would like to do the following (in abstract terms):
declare tmp record select column1 from tableA into tmp for each entry from above selection do insert into tableB values (tmp[column1], 0, 0, 0)
I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:
CREATE FUNCTION dbo.newDay (@mDate datetime) RETURNS int AS BEGIN DECLARE @id int DECLARE item_cursor CURSOR FOR SELECT id FROM tblKitchenCat OPEN item_cursor FETCH NEXT FROM item_cursor INTO @id WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate) FETCH NEXT FROM item_cursor INTO @id END CLOSE item_cursor DEALLOCATE item_cursor RETURN 0 END GO
I get a syntax error next to AS... what is it?
Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.
In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users ID Username
ChangeLog ID Description UserID DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM Users LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID) ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM ChangeLog LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID) ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
I'm a newbee to replication and wanted to know about the whole mechanism of replication and how is two way replication implemnted on 2 diffrent servers located at 2 different remote locations????
(i am not sure which forum to post this too) Hi, I imported a table onto my host's server, table_Login which has username password, and userID. userID is Primary Key & identity. When I had my code do insert of new user, I was getting error the Login.userID doesn't allow null values. Which shouldn't have mattered because userID is an identity. After making a diagram from Enterprise Manager, I noticed that I have two Login tables (although only one shows in Enterprise Manager's "Table" node view. The second table, which had none of the constraints that my table of the same name which is listed under the "Table" node does; appears to be the table which is being written to by my code. I can't find this table, Login(dbo), through Enterprise Manager, nor am i sure how to access it through Query Analyzer. Needless to say, I don't like this arrangement. and well tech support for this host seems to leave a lot to be desired. Anyone know what needs to be done so that I can fix it or fight with tech support to convince them that they should?