Browsing Questions About query (1)

MySQL has this incredibly useful yet properitary REPLACE INTO SQL Command. I wonder: Can this easily be emulated in SQL Server 2005? Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and Commit is always a little bit a pain in the a.., especially when doing it in the a…

For instance, my query is like the following using SQL Server 2005: SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') I have a full text index defined to use the column SearchField which returns results when using: SELECT * FROM Table WHERE SearchField LIKE '%c#%' I believe # is …

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name   id  Col1  Col2 Row1   1    6     1 Row2   2    2     3 Row3   3    9     5 Row4   4    16    8 I want to c…

I'm trying out the following query: SELECT A,B,C FROM table WHERE field LIKE 'query%' UNION SELECT A,B,C FROM table WHERE field LIKE '%query' UNION SELECT A,B,C FROM table WHERE field LIKE '%query%' GROUP BY B ORDER BY B ASC LIMIT 5 That's three queries stuck together, kindasorta.…

For certain types of sql queries, an auxiliary table of numbers can be very useful. It may be created as a table with as many rows as you need for a particular task or as a user defined function that returns the number of rows required in each query. What is the optimal way to create such a fun…

I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this: Category | Revenue | Yearh | Month Bikes 10 000 2008 1 Bikes 12 000 2008 2 Bikes 12 000 2008 3 Bikes 15 000 2008 1 Bikes 11 000 2007 2 Bike…

I haven't really done a ton of database work in a few years and have become unaccustomed with the tools available. Back a few years ago I was working with oracle databases and was using primarily TOAD with a little bit of MS Access as my tools of choice to prototype the queries I was using in my a…

I've got an interesting design question. I'm designing the security side of our project, to allow us to have different versions of the program for different costs and also to allow Manager-type users to grant or deny access to parts of the program to other users. Its going to web-based and hosted …

When writing database queries in something like TSQL or PLSQL, we often have a choice of iterating over rows with a cursor to accomplish the task, or crafting a single SQL statement that does the same job all at once. Also, we have the choice of simply pulling a large set of data back into our …

I have a large database and would like to select table names that have a certain column name. I have done something like this in MySQL, but can't find any info on SQL Server. I want to do something like: select [table] from [db] where table [has column 'classtypeid'] how can I do someth…

This is a very specific question regarding mysql as implemented in WordPress . I'm trying to develop a plugin which will show (select) posts that have specific ' tags ' and belong to specific ' categories ' (both multiple) I was told it's impossible because the way categories and tags are st…

I have a table with many millions of rows. I need to find all the rows with a specific column value. That column is not in an index, so a table scan results. But would it be quicker to add an index with the column at the head (prime key following), do the query, then drop the index? I can't …

If I have a query like: Select EmployeeId From Employee Where EmployeeTypeId IN (1,2,3) and I have an index on the EmployeeTypeId field, does SQL server still use that index?

Before someone gets in a tizzy because this was answered elsewhere (not sure if it was,but...), the 5 "related questions" shown right now do not match what I'm looking for, and I searched the string "sql insert" and did not find quite what I was looking for. So I'm basically trying to figure ou…

I have a database with two tables (Table1 and Table2). They both have a common column [ColumnA] which is an nvarchar. How can I select this column from both tables and return it as a single column in my result set? So I'm looking for something like: ColumnA in Table1: a b c ColumnA in Table…