sql

Updating email addresses in MySQL (regexp?)

Hi, Is there a way to update every email address in MySQL with regexp? What I want to do is to change something@domain.xx addresses to something@domain.yy. Is it possible to do with SQL or should I do it with PHP for example? Thanks! ...

Best practice for computing operation on consecutive records of a database: SQL or Java ?

For instance, I would like to compute the time elapsed between 2 events from the following table: CREATE TABLE Events (_ID INTEGER PRIMARY KEY, type INTEGER, time INTEGER) My question is should I compute this difference with SQL or with java code ? Actually, this does not seem to be easy in SQL while traversing the table in java and p...

MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

I expect the result of the third query below to contain id=732. It doesn't. Why is that? mysql> SELECT id FROM match ORDER BY id DESC LIMIT 5 ; +------------+ | id | +------------+ | 732 | | 730 | | 655 | | 458 | | 456 | +------------+ 5 rows in set (0.00 sec) mysql> SELECT id FROM email...

sql join question

I have the following tables nid timestamp title 82 1245157883 Home 61 1245100302 Minutes 132 1245097268 Sample Form 95 1245096985 Goals & Objectives 99 1245096952 Members AND pid src dst language 70 node/82 department/34-section-2 45 n...

IN vs. JOIN with large rowsets

I'm wanting to select rows in a table where the primary key is in another table. I'm not sure if I should use a JOIN or the IN operator in SQL Server 2005. Is there any significant performance difference between these two SQL queries with a large dataset (i.e. millions of rows)? SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.*...

What is the best free SQL GUI for Linux (MSSQL, MySQL, Oracle, Etc...)

Hello All, As I make the full switch from Windows to Linux (CentOS 5) I'm in search of the best free GUI SQL Client tool for MSSQL, MySQL, Oracle, etc... any suggestions? I've tried DBVisualizer (The best bet so far but still limited by the free version, not all functionality is there), MySQL GUI Tools (Good but only for MySQL, need oth...

Update multiple columns in a TABLE from another TABLE (Oracle)

I would like to update multiple columns in one table based on values in another. I think I know how to write an update statement in T-SQL that does what I want (haven't tested the below). Problem is I'm trying to translate this for an Oracle database. Does anyone know how to do the following in Oracle: UPDATE oldauth SET AUTHUNIQUENA...

SQL Server Bulk Insert

I want to import a one column text file into one of my sql tables. The file is just a list of swear words. I've written the following TSQL to do this BULK INSERT SwearWords FROM 'c:\swears.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) However it errors with unexapected end of file. The table im importing to is just an id...

SQL query: how do I change a value according to a lookup table?

[update: I am using MySQL 4.1.25 ] I think this must be a simple thing to do, but I'm a SQL noob and need some help. I have a lookup table that is something like: lookup_table key1, value1 key2, value2 key3, value3 ... keyN, valueN Then I have another table that has a random list of the keys (with repeats) in one column and I ne...

Is this a valid benefit of using embedded SQL over stored procedures?

Here's an argument for SPs that I haven't heard. Flamers, be gentle with the down tick, Since there is overhead associated with each trip to the database server, I would suggest that a POSSIBLE reason for placing your SQL in SPs over embedded code is that you are more insulated to change without taking a performance hit. For example. L...

Group by with count

Say I have a table like this in my MsSql server 2005 server Apples + Id + Brand + HasWorms Now I want an overview of the number of apples that have worms in them per brand. Actually even better would be a list of all the apple brands with a flag if they are unspoiled or not. So if I had the data ID| Brand | HasWorms ------...

How to execute Table SP in csharp

I looked at the SQLTeam website but now am having a new problem cause I have an IDENTITY column and their example does not. I have SQL Server 2008 and VS 2008. I am trying to execute the InsertPIF SP using C# and a table UDT, but am getting an exception. I have looked at a SQLTeam website example with a Table UDT, but their example...

Better SQL Query?

The db structure: fid subid fieldname fieldval To get a record for a person, I do something like this: $querystr = " SELECT FN.sub_id, FN.`First Name` , LN.`Last Name` , DOB.`dob` , EMAIL.`email` , PHONE.`phone` FROM ( SELECT sub_id, field_val AS 'First Name' FROM $db->data WHERE `field_name` = 'First Name' )FN, ( S...

Is there any way to use LINQ for MDX queries?

Anyone know if there are plans for LINQ to MDX . Does any way currently exist to use LINQ with MDX / CUBES ...

sp_tableoption in SQL Server

Hello everyone, I want to know and pros and cons of setting sp_tableoption of "table lock on bulk load" for SQL Server 2005/2008? My scenario is, I have 3 threads working on database, one query thread, one bulk insert thread and one delete thread. Here is the MSDN related link for sp_tableoption, http://msdn.microsoft.com/en-us/librar...

How to query multiple SUMs of the same item using SQL in iReport

I'm creating a JasperReport using iReport, and as such, I'm limited* to one SQL query. I have a table 'statistics', with a 'name' (VARCHAR), 'count'(INTEGER), and 'datetime'(DATETIME) columns. It is simple enough to get the sum of the 'count' column when the 'name' was "test" for the last day, and similarly for the last week, and month ...

How should I test a field against an SQL variable which can be null ?

I've got the following SQL : CREATE TABLE tbFoo( a varchar(50) NULL, ) CREATE NONCLUSTERED INDEX IX_tbFoo_a ON tbFoo ( a ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) insert into tbFoo ...

LINQ CompiledQuery.Compile and dynamic sorting?

This is a very short question and I fear the answer is short too. Is there any way of doing anything along the lines of Func<DataContext, string, bool, IEnumerable<X> fnMyQuery = CompiledQuery.Compile<DataContext, string, bool IList<X>( (db, sortColumn, sortDesc) => ( (!sortDesc)? db.OrderBy(x => DynamicRes...

Large Sqlite database search

How is it possible to implement an efficient large Sqlite db search (more than 90000 entries)? I'm using Python and SQLObject ORM: import re ... def search1(): cr = re.compile(ur'foo') for item in Item.select(): if cr.search(item.name) or cr.search(item.skim): print item.name T...

Oracle 10g PL/SQL- Select results as update column values

Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g? I have a table (call it Source) which has for simplicities sake, 2 columns, ID and KEY. ID Key ---- ---- 1 1000 2 1000 3 5000 4 1000 .. 101 8000 102 9000 103 7000 104 ...