i want to update to fields in one sql query.How do i do that? update tablename set field1= val1where id=1 now i want to update 2 fileds as follows .How can i do that? update tablename set field1 =val1 and set fied2=val2 where id=1 ...
Hi! I'll only try to present the main part of the problem, because the whole situation is much more complicated - I am unable to achieve the following with DetachedCriteria SELECT * FROM User LEFT OUTER JOIN GroupItem ON User.ID = GroupItem.UserID AND _groupItemRestrictions_ There can be multiple GroupDefinitions, User can belong to ...
Well given I have a value I want to check for potential matches in a database (in one varchar field) so I write something like: SELECT * FROM table WHERE column LIKE "%value%" Which will work if the value is something like "test" and the column has a value of "this is a test" however if it is reversed then I will not get a match I hav...
Right now using SQLite i write the below (I may move to either mysql or ms sql) command.CommandText = "SELECT count(reset_key) user_data WHERE reset_key=@key;"; This works but is there a more optimized or cleaner way? ...
What is the best way to randomize the time part for a DATE column, using Oracle 10g? For example, the date portion for the data was set as follows: UPDATE table_name SET column_ts = SYSDATE - 120 + MOD(ROWNUM, 35) I would like the time portion to have a different value for each row. ...
Hello, I'd like to display a string without the last 2 chars in a text field in Reporting Services 2005 vs2005. I tried several ways and if the string is empty or null I get an error: rsRuntimeErrorInExpression - The value expression for the textbox contains an error: Argument 'Length' must be greater or equal to zero. Here are the way...
Which Join having more I/O and CPU Cycle ? Hash join or Merge join or Loop join? ...
Following Uncle Bob's avice in Clean Code, I'd like to have no SQL in my PHP code. Presently I'm making use of Prepared Statements and I've factored my database access code into model classes. My initial thought is to break out my SQL into separate SQL files and then load them at runtime. Since this involves more disk IO (my gut feeli...
According to MSDN, Median is not available as an aggregate function in Transact-Sql. However, I would like to find out whether it is possible to create this functionality (using the Create Aggregate function, user defined function, or some other method). What would be the best way (if possible) to do this - allow for the calculation of...
I have a stored procedure where i am passing a simple XML : '<Products><ProductId>1</ProductId><ProductId>2</ProductId></Products>' I have a @temp table in SQL which has a ProductId Column: DECLARE @Temp TABLE( ProductId NVARCHAR(10) ) I need to write an insert statement which will loop through the ProductId's in the ...
I'm trying to count how many distinct value of FLOOR there is but I don't want the value "B" to count towards the total. Here is my current code. It counts how many distinct floors there is but it includes the FLOOR "B" when there is one. SELECT COUNT(DISTINCT FLOOR) as NB_FLOORS FROM TABLE_ID The table looks something like this : ...
I have a table like this... CustomerID DBColumnName Data -------------------------------------- 1 FirstName Joe 1 MiddleName S 1 LastName Smith 1 Date 12/12/2009 2 FirstName Sam 2 MiddleName S 2 LastName Freddrick 2 ...
I have a table ("dump") with transactions, and I want to list the total amount, grouped by category, per month, like: Month | Category | Category ID | SUM. The tables involved looks like this: TABLE dump: id INT date DATE event VARCHAR(100) amount DECIMAL(10, 2) TABLE dump_cat: id INT did INT (id in dump) cid INT (id in categories) TA...
Hi all, I'm planning a new ASP.NET project that will become a product that is installed by techinical staff that work in various companies. The program will be storing sensitive information that shouldn't be easily accessible to staff including the technical staff. I need to balance an easy straight forward installation with security a...
I have the following table EVENT_LOG: EVENT_ID: pk, int, not null TYPEID: fk, int, not null CATEGORYID: fk, int, null SOURCE: varchar(255), null DESCRIPTION: varchar(4000), null CREATED: datetime, null We've been creating a report, and found that performance sucks. There aren't any indexes aside from the clustered one. We could cr...
Can anyone provide details on what the three of these mean? System::ContainerStartTime System::CreationDate System::StartTime The documentation for these three is virtually non-existent. ...
I have a hashset that I want to serialize to a SQL Server table. When serialized hashset looks like this... <InstallerContactIds> <int>153771</int> <int>209572</int> </InstallerContactIds> I am using the following to insert the XML into the table... INSERT INTO dbo.cv_AssessorActionPlanInstallers SELECT @AssessorActionPlanId...
In SQL Server 2005, I have a table of input coming in of successful sales, and a variety of tables with information on known customers, and their details. For each row of sales, I need to match 0 or 1 known customers. We have the following information coming in from the sales table: ServiceId, Address, ZipCode, EmailAddress, HomePhone,...
I've been nosing around--and I'm no DBA myself,--but became curious when trying to troubleshoot a particularly expensive (and infrequent) query that performs an outer join on a linked server (2k5 to 2k5, different dbs on different physical machines). The issue doesn't generally seem to be execution time, but rather locking, as the outer ...
I would like to see the SQL statement that a given ActiveRecord Query will generate. I recognize I can get this information from the log after the query has been issued, but I'm wondering if there is a method that can be called on and ActiveRecord Query. For example: SampleModel.find(:all, :select => "DISTINCT(*)", :conditions => ["`d...