I have the following SQL result set (as result of a join FWIW): A,C B,E C,A D,A Every value represents a node. So the total set of nodes involved is A,B,C,D,E. Every row describes a directed edge. A -> C B -> E C -> A D -> A Of course this can be simplified to A <-> C B -> E D -> A Now I would like to filter out the rows that do...
So I have this table, with 3 columns: ID (unique key), PositionID, and SupervisorID. PositionID and SupervisorID are both foreign keys to a Positions table. What I haven't been able to figure out is a decently nice way of getting inherited subordinates. So for example: ID PositionID SupervisorID 1 2 1 2 2 ...
Is there a special way to declare a DateCreated column in a MS Sql Server table so that it will automatically fill it with the appropriate time-stamp when created? Or.. do I have to provide the datetime to it when I do the query, manually? ...
Using this SQL on SQL Server 2005 SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND COLUMN_NAME=@ColumnName I get the Primary Keys AND the Foreign Keys. How can I get only Foreign Keys? How can I see if a Constraint is a Primary or a Foreign Key? Thanks ...
I'm building (c#) sql select commands strings on the fly using LIKE %somestring% conditions. In my search strings I want to be able to handle any character found on a standard PC (US) keyboard (including ~ ! @ # % etc., alt-special chars not required but would be nice to have). I know that single quotes need to be doubled up and perhaps ...
Hi There, I have a very funny problem on my application, I get an error as follow: System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'. However, when I tried to speicify the provider on my connection as Provider=SQLOLEDB.1 or Provider=SQLOLEDB, then I get ano...
i hv to update a field in a table. i m using following querries. help me which one is right? update table1 set col1=<value>,col2=<value>.... from table1 t,table2 s where t.id=s.num and s.code='abc'; or update table1 set col1=<value>,col2=<value>.... where table1.id=table2.num and table2.code='abc'; which one is right?or both ar...
I have a (mysql) database table with the following columns: NAME | String (Unique) STATUS | int UPDATE_COUNT | int (Unique) I want the value of Max(UPDATE_COUNT) to reflect the cumulative number of updates performed on rows in the table. For example, starting with an empty table: Insert - (Name=John, Status=0) - // update count on...
Hi, I am actually having 100's of SP in my database. I have to find a set of 10 SP's in that which have a particular comment inside them. Is there any search query for this. ...
Hi, I have a database where each row has an id, a URL, and an XML. The IDs are unique, but URLs column can have duplicates. I need all the URLs, without duplicates, and for each URL I need the id and XML. If I ask only for the URL and the Id, I use the following query: select URL, max(ID) as ID from T1 group by URL And all is workin...
If a column is null, does that affect the space used by the column? Is the space used fixed by the column definition? Does this vary from database to database. (I am mainly interestred in SQL Server 2000.) Clarification: The question relates not to what happens when the column is 'nullable' (This costs another bit as Kritsen & gbn point...
I have 2 tables like which I would like to query to form a new table. table 1 number type serial index 1000001 613 3 1 1000001 613 3 1 1000001 613 3 1 1000001 613 3 1 1000001 613 4 1 1000001 613 3 1 table 2 number type serial index 1000001 613 ...
Imagine the following sql query: UPDATE MYTABLE SET COL2 = (SELECT COL2 + 1 FROM (SELECT MAX(COL2) FROM MYTABLE) AS X) WHERE ID IN (1,2,3,4,5) Assume that before the update is executed MAX(COL2) is 1. My intention is that for the update where ID=1 COL2 is updated to 'max(COL2) + 1' (i.e. 2), and that for subsequent updates 'MAX(COL...
declare begin for i in (select * from emp) loop if i.sal=1300 then update emp set sal=13000; end if; end loop; end; This code is updating all the records with salary 13000. Instead i want to update records having salary 1300 to the value 13000. Can you tell where I made a mistake? I am accesing records using i...
Setting the DBIC_TRACE environment variable to true: BEGIN { $ENV{DBIC_TRACE} = 1 } generates very helpful output, especially showing the SQL query that is being executed, but the SQL query is all on one line. Is there a way to push it through some kinda "sql tidy" routine to format it better, perhaps breaking it up over multiple lin...
Hey, I need to make a rather complex query, and I need help bad. Below is an example I made. Basically, I need a query that will return one row for each case_id where the type is support, status start, and date meaning the very first one created (so that in the example below, only the 2/1/2009 John's case gets returned, not the 3/1/200...
I use PostgreSQL database and C to connect to it. With a help from dyntest.pgc I can access to number of columns and their (SQL3) types from a result table of a query. Problem is that when result table is empty, I can't fetch a row to get this data. Does anyone have a solution for this? Query can be SELECT 1,2,3 - so, I think I can't ...
I am looking for a way to set the width on the returned column result from a SQL query. I run a query like Select Product from ConfigScheduling where Product is an Nvarchar(Max) column. The result in SQL Server Management Studio with Results to Grid is a 90 char wide visible column, with the full result present, but not visible. Thank ...
Out of interest when working with SQL statements should I always use the fully qualifed column name (tablename.columnname) even if only working with one table e.g. SELECT table.column1, table.column2 FROM table ...
Consider the following 2 tables: Table A: id event_time Table B id start_time end_time Every record in table A is mapped to exactly 1 record in table B. This means table B has no overlapping periods. Many records from table A can be mapped to the same record in table B. I need a query that returns all A.id, B.id pairs. Something lik...