I've got a 'task list' database that uses the adjacency list model (see below) so each 'task' can have unlimited sub-tasks. The table has an 'TaskOrder' column so everything renders in the correct order on a treeview. Is there an SQL statement (MS-SQL 2005) that will select all the child nodes for a specified parent and update the TaskO...
Basically the question is how to get from this: id string 1 A 1 B 2 C to this: id string 1 A B 2 C ...
This question comes close to what I need, but my scenario is slightly different. The source table and destination table are the same and the primary key is a uniqueidentifier (guid). When I try this: insert into MyTable select * from MyTable where uniqueId = @Id; I obviously get a primary key constraint violation, since I'm attemp...
Often I want to add a value to a table or update the value if its key already exists. This can be accomplished in several ways, assuming a primary or unique key is set on the 'user_id' and 'pref_key' columns in the example: 1. Blind insert, update if receiving a duplicate key error: // Try to insert as a new value INSERT INTO my_prefs ...
I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form: CREATE PROCEDURE load_stuff WITH EXECUTE AS OWNER AS INSERT INTO my_db.dbo.report_table ( column_a ) SELECT column_b FROM data_mart.dbo.source_table WHERE ...
I was helping out some colleagues of mine with an SQL problem. Mainly they wanted to move all the rows from table A to table B (both tables having the same columns (names and types)). Although this was done in Oracle 11g I don't think it really matters. Their initial naive implementation was something like BEGIN INSERT INTO B SELECT...
I was recently tasked with debugging a strange problem within an e-commerce application. After an application upgrade the site started to hang from time to time and I was sent in to debug. After checking the event log I found that the SQL-server wrote ~200 000 events in a couple of minutes with the message saying that a constraint had fa...
If I don't know the length of a text entry (e.g. a blog post, description or other long text), what's the best way to store it in MYSQL? ...
The problem itself is simple, but I can't figure out a solution that does it in one query, and here's my "abstraction" of the problem to allow for a simpler explanation: I will let my original explenation stand, but here's a set of sample data and the result i expect: Ok, so here's some sample data, i separated pairs by a blank line -...
Let me first say that being able to take 17 million records from a flat file, pushing to a DB on a remote box and having it take 7 minutes is amazing. SSIS truly is fantastic. But now that I have that data up there, how do I remove duplicates? Better yet, I want to take the flat file, remove the duplicates from the flat file and put the...
There are multiple delete rows questions here, but this one is a little unique (not difficult, just unique). I have already ready the other thread found here: http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows Question: I have a table with rowID,longitude,latitude,businessName,url, caption. This might look lik...
What is the best way to replace all '<' with < in a given database column? Basically perform s/<[^;]/</gi Notes: must work in MS SQL Server 2000 Must be repeatable (and not end up with <;;;;;;;;;) ...
I have two tables that are joined together. A has many B Normally you would do: select * from a,b where b.a_id = a.id To get all of the records from a that has a record in b. How do I get just the records in a that does not have anything in b? ...
I encountered the following ddl in a pl/sql script this morning: create index genuser.idx$$_0bdd0011 ... My initial thought was that the index name was generated by a tool...but I'm also not a pl/sql superstar so I could very well be incorrect. Does the double dollar sign have any special significance in this statement? ...
I have a bunch of tasks in a MySQL database, and one of the fields is "deadline date". Not every task has to have to a deadline date. I'd like to use SQL to sort the tasks by deadline date, but put the ones without a deadline date in the back of the result set. As it is now, the null dates show up first, then the rest are sorted by dead...
Given a database field named "widget_ids", containing data like "67/797/124/" or "45/", where the numbers are slash separated widget_ids... how would you make an update statement with SQL that would say: "if the widget_ids of the row with id X contains the text "somenumber/" do nothing, otherwise append "somenumber/" to it's current valu...
What's the best way of inserting information in table A and using the index from table A to relate to table B. The "solution" I tried is inserting the info in table A (which has a automatically generated ID), then, select the last index and insert it in table B. This may not be very useful, as the last index may change between the ins...
I have a submission table that is very simple: userId, submissionGuid I want to select the username (simple inner join to get it) of all the users who have more than 10 submissions in the table. I would do this with embedded queries and a group by to count submissions... but is there a better way of doing it (without embedded queries)...
I have a database scenario (i'm using Oracle) in which several processes make inserts into a table and a single process selects from it. The table is basically used as a intermediate storage, to which multiple processes (in the following called the Writers) write log events, and from which a single processes (in the following referred t...
I need to add the ability for users of my software to select records by character ranges. How can I write a query that returns all widgets from a table whose name falls in the range Ba-Bi for example? Currently I'm using greater than and less than operators, so the above example would become: select * from widget where name >= 'ba' and...