sql

Count parents backwords in one SQL Query?

I have a MySQL database (created by Wordpress) and this is similar to what it looks like: ID parentID otherStuff 54 55 this is a test 55 56 another test 56 0 last test What I need to do is to check how deep down a page is. I know that when it reaches parentID 0 it's finished. I could wri...

Why is this MySQL UPDATE taking forever?

I'm trying to figure out why one of our migration scripts is taking forever we are trying to do an update that joins from another table to get a relevant piece of data. Each table (A, B) has about 100,000 rows. # now populate the ACHIEVEMENT_INSTANCE.OBJECTIVE_INSTANCE_ID update A a, B b set a.INSTANCE_ID = b.INSTANCE_ID where a.ID = b...

formatting results using sql select with jet

I have data in Excel files that I am pulling out using the MS Jet OLEDB provider. The results of my select are being passed into a component that I cannot change to generate an html table on the fly. Some of my data files contain numbers that I need formatted with commas (#,###). As I said I cannot change the output component to forma...

How to count rows that have the same values in two columns (SQL)?

I am sure there must be a relatively straightforward way to do this, but it is escaping me at the moment. Suppose I have a SQL table like this: +-----+-----+-----+-----+-----+ | A | B | C | D | E | +=====+=====+=====+=====+=====+ | 1 | 2 | 3 | foo | bar | << 1,2 +-----+-----+-----+-----+-----+ | 1 | 3 | 3 | biz | ...

Way of searching 30,000 SQL Records

I am about to make a simple search facility on my website, where a user will enter around 2-4 keywords which will get searched in two columns in a table in my MS SQL database. One column is a varchar (50) called title and one column is a varchar(2500) called description. There will be about 20,000-30,000 records potentially at any one ti...

Writing a complex trigger

I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRowID is updated, I want to get the values from Applicant.AppStatusRowID, Applicant....

Mysql group by substring

I have a table with the following structure: id bigNumber text 1 1200321030011010233 "an item with some text" 2 1200321030011014563 "another item with some more text" 3 3120323434432211133 "more...." ... ... The table contains approximately 50.000 records. I want to do the following query but it is slow: SELECT COUNT(*), ...

What is the preferred merge method for SQL Server 2005?

I have mainly been using the Exists Method for merging a row into a table but I am considering switching to the Row Count Method. Is there any reason not to? Exists Method If Exists(Select * From Table Where ID = @ID) Begin Update Table Set Value = @Value Where ID = @ID End Else Begin Insert Into Table (Value) Values (@Va...

Subquery using Exists 1 or Exists *

I used to write my EXISTS checks like this: IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters) BEGIN UPDATE TABLE SET Columns=VALUES WHERE Columns=@Filters END One of the DBA's in a previous life told me that when I do an EXISTS clause, use SELECT 1 instead of SELECT * IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters) BEG...

Is using a SELECT inside a pipelined PL/SQL table function allowed?

The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technically, it is possible to use SELECTs withou...

How to create DB in SQL Express using SQL commands?

I am reading a book on SQL and it gives me scripts to run to create a database. But I have only SQL Express 10, which doesn't look like has tools to run SQL commands. Is there any way to run the scripts? ...

TSQL- Rollup SQL 2005

I have the following example code: create table Details( name varchar(20), age int, weight int, recordDate Datetime) --insert data ..query: SELECT a.name, a.age, a.recordDate, a.weight - (SELECT b.weight FROM Details WHERE b.recordDate = dateadd(dd, -1, a.recordDa...

Suggested indices for a MPTT table

I'm just building a table to store hierarchical data using the Modified Pre-order Tree Traversal (MPTT) -- you know the one: each node stores the left and right IDs to find its descendants. I'm using a the CakePHP suggested model, which varies from the standard way by including the parent_id with each row. Here's the suggested table str...

MySQL - Search and replace table1.field's contents with contents of table2.otherfield

I need a MySQL query that will do the following: Instead of the alpha team names in the 3rd column of the players table, I want it to reference teams.id table: players id player_name team ------------------------------------------ 1 Sue Smith Silly Chimps 2 Mike Olson Black Cats 3 ...

DateTime manipulation in SQL Server EXECUTE sp_executesql

I am trying to do the following: EXECUTE sp_executesql N'SELECT TOP 10 * FROM dbo.Items WHERE DateCreated BETWEEN @start AND @end' , N'@start DATETIME, @end DATETIME' , @start = '20091001' , @end = GETDATE() --problem is caused by this line Error: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ')'. I need to...

Running a query without GROUP BY and aggregation functions

Let's say that you are limited to use only this syntax (you can't use aggregation functions like MAX or MIN, and neither you can't use GROUP BY clauses; please don't ask me why): {SQL query} ::= SELECT [DISTINCT | ALL] [TOP {integer}] {select_list} FROM {table_reference} [WHERE {search_con...

little complex sql

i have album table and album permission table. album album_id name private (values yes or no) album permission id album_id user_id **albums** album_id name private 1 family yes 2 friend no 3 outdoor pic yes **album permission** id album_id user_id 1 3 5 now i want to get all ...

Updating a summary table based on 2 other tables.

Commisions (commisionID INT, EmployeeID, amount INT, created Datetime) Sales (saleID INT, EmployeeID, amount INT, created datetime) The summary table: Employee (employeeID, totalCommisions INT, totalSales INT, created DateTime) There can be 0 or more rows per employee in both Commissions and Sales tables. Query#1 The query is to u...

Oracle DB - How to perform a batch search an replace across all the tables in a DB

I want to perform a search for a specific value "04L" in the column across all the tables,procedures,functions etc in the DB. I also want that output ( table,procedures that have same value) to the text file i.e the tables , procedures functions that have that value. Any help appreciated. ...

Select * from table1 that does not exist in table2 with conditional

I have 2 tables. One is a table with things that can be learned. There is a JID that desribes each kind of row, and is unique to each row. The second table is a log of things that have been learned (the JID) and also the userid for the person that learned it. I am currently using this to select all of the data for the JID, but only t...