sql

How does one write a DELETE CASCADE for postgres?

I'm manually constructing a DELETE CASCADE statement for postgres. I have a 'transaction' and a 'slice' table, related as shown below: Table "public.slice" Column | Type | Modifiers ----------+------+----------- id | text | not null name | text | Referenced by: TABLE "transaction" CONSTRAINT "transaction_slice_...

select null as testdate into #temp_table

Hi All, Is there a way to insert/update a datetime value (getdate()) into a temp table created like the following: select id, null as testdate into #temp_table and then later statement: update #temp_table set testdate=getdate() i get error: "cannot convert datetime into int..." Thanks, rod. ...

What do I need to know about working with huge databases?

I want to know what specific problems/solutions/advices/best-practices[don't punish me for the word] are arising while working with huge databases. Under huge I imply databases, which have tables with millions of rows and/or databases with petabytes of data. Platform-oriented answers will be great too. ...

Does Adaptive Server Anywhere not support subqueries in the FROM clause?

I am trying to execute an SQL query on an Adaptive Server Anywhere database. Here it is: SELECT count(*) AS s FROM ( SELECT column1, count(*) AS n FROM table1 GROUP BY column1 HAVING n > 1 ) In the subquery, I want to get all the rows that are duplicates and in the outer query I want a count of the rows that are dupli...

Select three rows when two of them need to be unique (SQL)

First of all I am using Oracle 10g Express So there are three columns I want to select: [domain_name] [index_path] [collection_name] Now there are two columns that I want to be unique: [domain_name] [index_path] So my issue is how do I basically: select unique domain_name, index_path from TABLENAMEHERE while also selecting the c...

SVN client for SQL Server Management Studio 2005

I have installed Tortoise SVN and AnkhSVN, but neither one shows up in my SQL Server Management Studio 2005 under the Source Code Control options. I know they work because all my Visual Studio projects are working fine. Do I have to do install a different SVN client to integrate to SQL Server Management Studio? ...

what could be the datatype for the column having weblinks.

hi all, One curious question. if i have a table with column with weblinks then what could be the datatype nvarchar or varchar. and what could be the size of that datatype? ...

Teradata Optimizer Equal vs Like in SQL

I am currently trying to optimize some bobj reports where our backend is Teradata. The Teradata optimizer seems very finicky and I was wondering if anyone has come up with a solution or a workaround to get the optimizer to treat likes in a similar regard to equals. My issue is that we allow the user to input one of two methods: 1. Ente...

Mysql JOIN - Unexpected result in mysql_fetch_array

I am using a complex join statement to get data from my 'items' table where 'column1' is equal to the value of table2.ID This is in a mysql_query() statement, and it should return 3 rows. Now my understanding is that by using $array=mysql_fetch_array($queryresult);, I can then loop through each 'row' using a foreach($array as $output){...

Need SQL Query for this scenario

Table structure: ProjectID ParentID Name Path 38 2 client |2|38| 108 38 Sample1 |2|38|108 205 38 Sample2 |2|38|205 206 38 Sample3 |2|38|206 207 38 Sample4 |2|38|207 Query: SELECT Name, ProjectId FROM dbo.Projects project WHERE Parent...

How can I optimize this MySQL query?

Hello, can someone please explain why the addition of the group by subquery makes this query takes so long (30secs): SELECT * FROM aggregate_songlist AS a INNER JOIN musical_works AS m ON a.musical_work_id = m.id WHERE m.genre='rock' AND m.id NOT IN (SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING C...

How can I INSERT data into two tables simultaneously in SQL Server?

Let's say my table structure looks something like this: CREATE TABLE [dbo].[table1] ( [id] [int] IDENTITY(1,1) NOT NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC) ) CREATE TABLE [dbo].[table2] ( [id] [int] IDENTITY(1,1) NOT NULL, [table1_id] [int] NOT NULL, [data] [v...

sql help please - get latest activity for customer

Hi All, Given 2 tables CustomerActivity CustomerId, ActivityId, CreatedOnDate 1, 1, 8/1/2010 1, 2, 8/15/2010 2, 1, 7/24/2010 2, 2, 8/15/2010 TempUpdateTable CustomerId, RecentActivityDate 1, NULL 2, NULL How do I fill in the NULLs in TempUpdateTable using the CustomerActivity table? My first attempt didn't pan out: UPDATE [TempUpd...

Access function return value in joining table column

The partial select statement is: SELECT t1.Column1, t1.Column2, **"Column3"** = dbo.FunctionName(Column1, Column2) FROM Table1 t1 Now I wanted to left join Table2 in above query but join needs to be "Column3" = Table2.Column3; something like as below query which does not work and how to achieve that. SELECT t1.Column1, t1.Column2, ...

Select three rows, two of them (grouped) unique, other filtered by another column (SQL)

First of all I am using Oracle 10g Express So there are three columns I want to select: [domain_name] [index_path] [collection_name] Now there are two columns that I want to be unique (as a group): [domain_name] [index_path] And then I want to select the row baised on when another column [gen_timestamp] is most recent. So my issue ...

MS Access data export performance is horrible

Hello, I'm using Access 2007. I have a query that accesses a table with a few hundred thousand records in it, which I am joining to another table via an inner join based on two fields. The joining fields are indexed in both tables, and the sort column is also indexed. When I run the query straight up, the result set opens up in about...

Optimize massive MySQL INSERTs

Hi! I've got an application which needs to run a daily script; the daily script consists in downloading a CSV file with 1,000,000 rows, and inserting those rows into a table. I host my application in Dreamhost. I created a while loop that goes through all the CSV's rows and performs an INSERT query for each one. The thing is that I get...

Split column to multiple rows

Hi! I have table with a column that contains multiple values separated by comma (,) and would like to split it so I get earch Site on its own row but with the same Number in front. So my select would from this input table Sitetable Number Site 952240 2-78,2-89 ...

Visual Foxpro SQL Server - Can't find the Call to SQL server in Foxpro

My DBA's are saying my foxpro application or .DBC (Database container) are hitting SQL server but searching all the code can't find the SQL call (FMTONLY ON/OFF). This is the SQL command being sent: FMTONLY ON/OFF Getting called 16260 times every few minuets? Any ideas how to find this or what could be causing it, maybe my DBC file? ...

sql server: is it possible to convert 1,123 to number?

i have a field that is a varchar and the string is stored as "1,223" or "23,342,234" they have comas! is it possible to read them as numbers? i dont like any of these answers. in mysql reading a varchar 1,123 and converting it to a number was no problem at all. ...