in our database schema, we like to use delete flags. When a record is deleted, we then update that field, rather than run a delete statement. The rest of our queries then check for the delete flag when returning data. Here is the problem: The delete flag is a date, with a default value of NULL. This is convenient because when a record ...
I recently saw someone post this as part of an answer to an SO query question: SELECT DISTINCT a, b, c FROM t1 WHERE (a,b,c) NOT IN ( SELECT DISTINCT a,b,c FROM t2 ) I'm a bit confused, as I always thought that you can't use multiple columns for "NOT IN" ("where(a,b,c)", etc.). Is this correct SQL syntax? And how about MySQL? ...
Background Information Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syntax for the GROUP BY clau...
I'm having a problem running this query: DELETE FROM [IRPROD]..[BUDGET_USER].[GL_EXP] WHERE FISCAL_YEAR = 2010 IRPROD is a linked server to an Oracle 10g database. It's linked using the Oracle OleDB provider. There are ~79000 records that need to be deleted. Running this query it deletes 54. Running it again gives me this error messag...
The error message I'm obtaining when trying to drop a column: The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'. Msg 5074, Level 16, State 1, Line 43 ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column. I have already tried to find the default constr...
Im using the symfony framework with mysql. I have a field in mysql that is generated by doctrine as: weight: { type: double, notnull: false, default: NULL } `weight` double(18,2) NULL DEFAULT NULL The value is entered using a textbox and the generated sql trys to insert '' into this field if no value is given. This produ...
Hello, I have a mysql query, that does the following, SELECT * FROM categoryTable LEFT JOIN userMenuTable ON userMenuTable.categoryId = categoryTable.categoryId this returns only results that match this condition ON userMenuTable.categoryId = categoryTable.categoryId I was hoping it would be possible to pull all the results and ...
Are SQL Server database Ids always positive? As in the dbid from sysdatabases. SELECT name, dbid FROM master.dbo.sysdatabases This question has nothing to do with identity columns or primary keys. ...
consider my sql query below; it is calling sum twice for the same argument. IS this duplicating the work done by the server. Is there a better way to do this? SELECT Status_Detail_Code, count(*) as [Number of times assigned], round(sum(Duration)/60,2) as [total duration Hr] FROM dbo.V_TIMELINE WHERE (CADATE > N'...
I have generated a Matrix report using SQL reporting services from a table in Database. Table Date - Item - cost 10/31/2009 - a - 1 10/31/2009 - b - 2 11/30/2009 - a - 5 11/30/2009 - b - 6 12/31/2009 - a - 9 12/31/2009 - b - 10 SQL Reporting Matrix Report Item - 10/31/2009 - 11/30/2009 -...
Hello, I have a customers table with information about our customers (ID, login, name, contacts info, different options, TS column, and so on, ~15 columns, ~few hundreds of customers). Now we need to send every-day-updates to our biggest customers (<10% of all customers). And I need to store timestamp of the latest update which were s...
We are trying to model a school system hierarchy from State>County>District>School, hence we are using the model below to describe the parent child relationship along with organization type (i.e. School, District,…). Organization Hierarchy Now we need to store details on each particular organization, for example if it’s a school we n...
I had a table modified to add status column to it in this fashion ALTER TABLE ITEM ADD COLUMN STATUS VARCHAR DEFAULT 'N'; However SQLite doesnt seem to add N to the column for any new ITEM created. Is the syntax wrong or is there any issue with SQLite and its support for defaults. I am using SQLite 3.6.22 ...
In a SQL query, does adding "TOP 1" to SELECT TOP 1 [values] FROM [TABLE] where [TABLE].Value = "ABC" give me a performance increase, when I know there is only one of those records? Specifically I'm thinking about LinqToSql and the difference between the methods .Single(...) and .First(...), where .First(...) adds the TOP 1 to the g...
I need a way to be able to select from a table if that table doesn't contain data of a certain type. For example if we have a table called farm and another table called animal. Now FarmA contains a pig and a goat and FarmB just contains a goat. I want to select all farms that contain no pigs. My first try was to do this: SELECT f.* F...
I have a column whose values consist of a prefix and then some value, e.g.: ABC-Car ABC-Dog ABC-Suit CBR-Train In my query I would like to trim off certain prefixes, e.g. if I trimmed off 'ABC-' it would give me the following: Car Dog Suit CBR-Train The only problem is, all characters are valid for the part after the prefix. So it...
I do like the SQL editor now bundled with Eclipse but I can't seem to find a way for it to format my code like eclipse will with my java. Did I miss something, or does anybody have any alternatives? Thanks ====EDIT==== I'd also be happy if there was an alternate plug-in that someone could recommend. ...
I'm trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error: function similarity(character varying, unknown) does not exist If I add explicit casts to text I get the error: function similarity(text, text) does not exist My query is: SELECT (similarity("tabl...
Database structure with two 1-n connections. User table ========== user_id Attribute table =============== attribute_id user_id attribute_name Attribute_Value table ===================== attribute_value_id attribute_id attribute_value Is there a way that I can receive the data in the following row style: user_id | firstname | lastn...
Hello, I'm building a stored procedure. This stored procedure needs to insert a record if a record with a specific value does not exist. If the value does exist, I need to update the record. The problem I'm having is determining if a record with the given value exists or not. I am using the following code: DECLARE @record1ID as char(36)...