I need to do something like this in sql: declare @StartDate varchar(10) declare @EndDate varchar(10) set @StartDate='12/31/2008' set @EndDate='1/11/2009' Declare @date varchar = @StartDate while (@date <= @EndDate) begin -- some statements set @date += 1 -- basically increment by 1 day end How can I do the above correctly in SQL? B...
Is there a way to format a sql date like this: 3/11/2009 (as opposed to03/11/2009) 20/7/2008 (as opposed to 20/07/2008) Thanks! ...
I only want to update 1 row (id) and only IF the status is == to stat UPDATE link_list SET status=0 WHERE id=@id AND status=@stat; Now i want to delete all rows but ONLY if the above was affected, how do i change the below to only delete if the above was affected? DELETE link_list WHERE linkId=@id; ...
Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more efficient: #1, SELECT u.name FROM user u INNER JOIN user_profile p ...
I am trying to do an SQL query, but I need to check somehow if the value is an email address. I need a way to check if $user is an email address, because I have user values such as this in my table. test test2 [email protected] [email protected] test392 [email protected] and so on... I need to make it so $useremail checks $user to find...
Ok.. So I'm trying to improve my SQL skills and have a question. Here is a screen shot of the schema. (http://img509.imageshack.us/img509/97/screenhunter02nov121946.gif) Alright so I'm selecting a bunch of Report Bundles and other rows from a table you can see. I've got these two tables joining together correctly and displaying what s...
What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2 ...
We have a restaurant table that has lat-long data for each row. We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc. We have the following query for this purpose: ***Parameters*** Longitude: -74.008680 Latitude: 40.711676 Radius: 1 mile ***Query*** SELECT * FR...
Hello all, I'm running SQL queries on Oracle 10g. I have two tables ( sample data provided below ). i'm trying to extract some fields from Table t2 and update the empty columns in table t1. I'm encountering the following error: ORA 01722: Invalid Number ( pointing to *REGEXP_SUBSTR* ) I understand this is because of non - numeric dat...
Hi everyone, I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here's my question: The subquery below returns a Table with 3 columns, USERNAME, COMPANY, POSITION. I just want to get the USERNAME column out without altering the subquery within. I tried ...
I'm running a query using "EXEC sp_helptext Object", but it returns multiple lines with a column name Text. I'm trying to concatenate that value into a single string but I'm having trouble trying to figure out the best way to do it using T-SQL. ...
please tell me how to write this query i have an access table number 2 2 1 2 2 1 1 3 2 i want a query that gives number count 2 5 1 3 3 1 any help appreciated ...
Is there anyway I can erase all the duplicate entries from a certain table (users)? Here is a sample of the type of entries I have. I must say the table users consists of 3 fields, ID, user, and pass. mysql_query("DELETE FROM users WHERE ???") or die(mysql_error()); randomtest randomtest randomtest nextfile baby randomtest dog anothert...
We have a (large) SELECT query, that can take ~30 seconds to run. I am told that when placed in a view, it takes less than 5 seconds to run. My assumption is that SQL Server caches query plans for queries that don't change, so why the massive improvement in performance here? Just to be clear, this really is just a case of taking somet...
I have two MS Access tables: tableA num state 1 12 2 13 1 11 3 12 tableB num stateA stateB 1 12 11 1 12 11 2 13 12 2 12 11 1 12 11 1 15 11 3 12 11 How can I create a third table which will have only one column num - appended from two table...
I'm trying to do something like this in Progress SQL (THIS IS NOT POSTGRES!) SELECT CASE WHEN code LIKE '%foo%' THEN 'Y' ELSE 'N' END as foo FROM bar However Progress does not support a LIKE operator. INSTR looks like it might do the job, but it is a Progress extension an isn't supported on the DB I am using. Is there another ...
Hi I have a simple nHibernate criteria query which is looking for records with a datetime less than today: example: criteria.Add(Expression.Le("TheDate", DateTime.Today)); However, the results I am getting are incorrect when the day/month is ambiguous (eg 12th November 2009 returns the records for 11th December 2009) I have looked ...
i have two access tables tableA num count 1 7 2 8 3 9 4 9 5 13 6 6 tableB num count 0 1 1 14 2 12 3 5 4 5 5 11 6 5 how can i create an access query that will ignore the numbers which have count less than 6 in any of the two tables. i.e. 0,3,4 & 6 and create a table with the rest of the numb...
I am designing a table that has several fields that will be used to record weight and lengths. Examples would be: 5 kilograms and 50 grams would be stored as 5.050. 2 metres 25 centimetres would be stored as 2.25. What T-SQL data type would be best suited for these? Some calculation against these fields will be required but using a de...
Is it possible to get the height of a 'text' field from mysql? Or the number of linebreaks would also work! I have a table which fetches the 'text' field from my mysql database. The background image of the table is sort of a container... I want the background to change to another (taller) background if the text is vertically high! Poss...