sql

Rollback Multiple SQL update queries in MS Access

I have multiple SQL stored procedures (e.g. UPDATE, SELECT INTO statements) executed in VBA in MS Access: CurrentDb.Execute "qry1" CurrentDb.Execute "qry2" I want it so that: * if qry2 fails, it will undo qry1. * qry1 and qry2 are executed at the same time, (as I have many of these stored procedures executed in a chain), so the procedu...

SQL Count(*) on multiple tables

Hi all, I am writing a horse racing web app and i have a stats page. What i am trying to do is show how many winners there are from each year. Currently i have this: SELECT `Horse Number`, Count(*) AS `Total Winners` FROM `races`.`2009` WHERE `Win $`>0 GROUP BY `Horse Number` ORDER BY Count(*) DESC; It works like a charm and return...

SQL Execute Insert or Update based if the Data is already in the Table

is it possible to execute a query that inserts the Row if the ID don't exist and Update the Row if the ID already exists in a single sql statment? i mean not using stored procudures just plain SQL. i already doing LoadRecord and if noone returned then insert and if any record is returned then update. but i wanted to know if there is any ...

How do I make DBX know that fields from joins shouldn't be updated during ApplyUpdates?

I've got some code that builds a map (graph) of rooms on a grid with links between them. It's stored in a Firebird database with rooms in one table and links in another. The data is managed through DB Express TSimpleDataset datasets. The query for the Exits (links) table looks like this: select EXITS.*, r1.x as x, r1.y as y, ...

how to denormalize ranges and store them in a data warehouse?

My custom web servers report session information in the form of a wall clock timestamp (start of session) and the number of wall click seconds the session lasted. I want to store this information in a data warehouse (MySQL + start schema) in a way that will allow me to for example query the number of sessions for a particular entity a...

join query help !

Hey, I am dealing with some queries for my assignment and any help would be greatly appreciated. List branches together with the number of employees and assigned to them customers, total loan amount, total account balance, assets for branches residing in the given city. List customers who made account and loan operations in the given ...

Create local Linked Server using Server Name or IP Address?

Based on the answer I received on this question (http://stackoverflow.com/questions/1911969/...) I have set up a Linked Server on SQL Server 2008 to a Pervasive database. Since both databases are on the same physical server, what would be the recommended method to create the linked server, using the IP Address of the server or the Serve...

Is Custom Text supported in Computed columns?

Is there any way of adding custom text in an computed column? For example this formula works great ([Duration] + '12') Could i have a result, from a computed column, similar to this one? ([Duration] & ' MyCustomText') Can i add custom text in a computed column? Or am i asking too much? ...

Better performance for SQLite Select Statement

Hi Guys, I'm developing an Iphone App where the user types in any string into a searchbar and presses the search button. After that a result list should appear. In my SQLite I have four columns a, b, c, d. Let's say they have the following Values: Dataset 1: a: code1 b: report1 c: description1_1 d: description1_2 Dataset 2: a: code2 ...

SQL statement - update on two tables

Hello - I am a newbie to SQL. please help with this. I have the below SQL which updates a table for company ticker = SUN from another table that has many records for ticker 'SUN' and I need to use only one record matching 'Float' the below works if I state 'SUN', however, I want to do this for 4000+ ticker symbols. How do I do it? UPD...

Sql query has unwanted results - Where is the problem?

Hi, I have a table which records users's scores at a game (a user may submit 5,10,20,as many scores as he wants). I need to show the 20 top scores of a game, but per user. (as a user may have submitted eg 4 scores which are the top according to other users's scores) The query i have written is: SELECT DISTINCT `table_highscores`.`us...

how to obtain ranges of serial numbers in sql?

I have a table with a field called sector, each sector is usually something like 1,2,3,4,5,6,7,etc. I want to show available sectors in an application, I thought that showing all 1,2,3,4,5,6,7 is dumb so I should show "1 to 7" instead. The problem is that sometimes the sectors skip one number like this 1,2,3, 5,6,7. So I want to show s...

How to write MySQL REGEXP?

A table contains the string "Hello world!" Thinking of * as the ordinary wildcard character, how can I write a REGEXP that will evalute to true for 'W*rld!' but false for 'H*rld!' since H is part of another word. 'W*rld' should evalute to false as well because of the trailing '!' ...

Mysql query won't ORDER BY date

Hi all, I am stuck with the following query, which won't order by it's date. Any help, or insight into what I am doing wrong will be much appreciated. The query is supposed to get an entry by thread_id, and then show the newest post in the thread, much like with a forum post, which it does fine. But when I try to order the results from ...

SQL Server 2005 DBA Learning Resource

So I have some experience with Microsoft Access, building database apps for people, vba, etc for different folks at work....different divisions. And I have actually learned a lot in that realm...however, now the need for SQL Server has arrived, and I have never really ventured into that realm...so let the questions begin: how vastly di...

(mySQL) Unable to query 2 tables properly for data

I have 2 tables. One is 'page_links' and the other is 'rpp'. Table page_links is the superset of table rpp. The following is the schema of my tables: -- Table structure for table `page_links` -- CREATE TABLE IF NOT EXISTS `page_links` ( `page` varchar(255) NOT NULL, `page_link` varchar(100) NOT NULL, `heading_id` tinyint(3) uns...

Is there a "for xml path" equivalent in LINQ to SQL?

I have a table of blog entries, a table of tags, and a table that intersects the tags to a blog entry. I want to roll-up the tags of a blog entry into a comma delimited string to be returned in the same result set. This is how I've done it in SQL: select     be.Title     ,Tags = lower((         select             stuff((             ...

MySQL AVG function for recent 15 records by date (order date desc) in every symbol

I am trying to create a statement in SQL (for a table which holds stock symbols and price on specified date) with avg of 5 day price and avg of 15 days price for each symbol. Table columns: symbol open high close date The average price is calculated from last 5 days and last 15 days. I tried this for getting 1 symbol: SELECT avg(cl...

mysql daily count grouped by one column

I have the following: SELECT COUNT(*),DAYNAME(sip_Date) AS DAY,sip_callid FROM request_line WHERE DAYNAME(sip_Date) = 'Saturday' GROUP BY sip_callid; with an output: +----------+----------+-------------------------------------+ | COUNT(*) | DAY | sip_callid | +----------+----------+--------------------...

PostgreSQL: Selecting N result in group by query

Hi, I have following SQL query that selects some results from my table: select avg(c3), count(c3), std from ssims where obraz = 'lena' group by std order by std But I have various number of tests performed for different values of std, so it returns me something like that: 0.906176136363636;44;5 0.881669302325582;43;10 0.855873409...