sql

Is there a way i can do multiple inserts into one table using a condition?

Is there a way i can do multiple inserts into one table using a condition? i have a list of subscribers in tbl_subscribers. i have an update on productX so i would like everyone who is subscribes to productX to get a notification. The user_notification table is id PK, user_id, notification_id. The two values i need is product_id (produc...

Distinct one column and display other columns below

I am sure I am looking at this in the wrong way. I would like to hold a restaurant menu in a database and display it. For example, I have created a table similar to the one below: Columns: Food_Item | Food_Description | Food_Price | Food_CAT. Data: Salad | Refreshing Salad | 5.25 | Starters Prawn Cocktail | Lovely Prawns | 4.75...

Mysql - Alter a column to be AUTO_INCREMENT

I'm trying to modify a table to make it's primary key column AUTO_INCREMENT after the fact. I have tried the following sql, but got a syntax error: ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am I doing something wrong or is this not possible? +--------------------+ | VERSION() | +--------------------+ | 5...

Introspecting DEFAULT values and computed columns in Sybase ASE

For a Perl library that dumps Sybase schemas for DBIx::Class (::Schema::Loader), I need to be able to introspect DEFAULTs and computed columns. Suppose we have: create table bar ( id INTEGER IDENTITY PRIMARY KEY, foo VARCHAR(10) DEFAULT 'foo', adt AS getdate(), ts timestamp ) Here's as far as I got: select substring(c.name,1...

How to design a database for templated logging purposes?

In my web application I want to log certain user interactions etc, like "User ABC joined Group XYZ" Therefore I want to set up a logging mechanism that logs into a Mssql database. I'm now trying to find a good database design to achieve flexibility. First of all, I don't want to log strings like "User ABC joined Group XYZ". I'd like to...

Do any databases support automatic/timed closing of databases?

We would like to implement an application architecture in which we have large numbers of databases on the disk, one database file for each customer. When a user request comes in the database is opened (if it isn't already). After a certain period of time with no activity, the database is closed automatically by the server, thereby free...

Oracle: pivot (coalesce) some counts onto a single row?

update: what I was calling coalesce I should have been calling pivot. I'm extracting some daily usage counts from a log table. I can easily get this data one row per date/item, but I would like to pivot coalesce the columns into a single row. e.g., I have: date item-to-be-counted count-of-item 10/1 foo 23 10/1 ...

SQL search and destroy duplicates

I have a table with fields (simplified): id, fld1, fld2, fld3. id is a numeric primary key field. There are duplicates: id differs but fld1, fld2 and fld3 are identical over 2 or more rows. There are also entries where the values occur only once, i.e. non-duplicates, of course. Of each set of duplicate entries, I want to retain only...

[SQL] Is it possible to update a fields value with the value of the field in the previous row?

I have a table from which I would like the update the value of a certain colum of fields. Basicly moving one value down and those under it should inherit the previous value of the one about them. I wonder if this action is possible using a single SQL query. My table: CREATE TABLE `menu` ( `slot_index` int(2) NOT NULL, `language_...

SQL: Filtering data using a join is bad?

For example, I have the following tables: animal ----------------------- animal_id | animal_name ----------------------- owners ----------------------- owner_id | owner_name ----------------------- owners_animals -------------------- owner_id | animal_id -------------------- I want to find the animals with no owners so I do the quer...

Can SQL Server Pivot without knowing the resulting column names?

I have a table that looks like this: Month Site Val 2009-12 Microsoft 10 2009-11 Microsoft 12 2009-10 Microsoft 13 2009-12 Google 20 2009-11 Google 21 2009-10 Google 22 And I want to get a 2-dimension table that gives me the "Val" for each site's month, like: Mont...

Joining multiple (4) tables in MYSQL

I have four tables I want to join and get data from. The tables look something like... Employees (EmployeeID, GroupID[fk], EmployeeName, PhoneNum) Positions (PositionID, PositionName) EmployeePositions (EployeePositionID, EmployeeID[fk], PositionID[fk]) EmployeeGroup (GroupID, GroupName) [fk] = foreign key I want to create a query t...

what's the best way to design dynamic key-value pairs in mysql?

Hi, I need to attach unlimited attributes to a record in a table, and I've already designed a system using #3 below using mysql. Unfortunately I am finding that searching across a million records is getting slow. Is #2 a better approach, or is there a better way alltogether? Is this a case for using a view? I'd like to have my keys ...

In which order Rails does the DB queries

In http://stackoverflow.com/questions/2170001/select-n-objects-randomly-with-condition-in-rails Anurag kindly proposed this answer to randomly select n posts with votes >= x Post.all(:conditions => ["votes >= ?", x], :order => "rand()", :limit => n) my concern is that the number of posts that have more than x votes is very big. what ...

AMO in c# project

I am building and deploying a SSAS cubes using AMO. I need the complete object model for AMO as an XSD (in schema form). I currently have built a schema by hand based on things that I need, but I'm afraid there would be elements that will come up during development time later on that will cause the upstream process that fills an XML f...

Seeding SQLite RANDOM()

Does SQLite support seeding the RANDOM() function the same way MySQL does with RAND()? $query = "SELECT * FROM table ORDER BY RAND(" . date('Ymd') . ") LIMIT 1;"; From the MySQL Manual about RAND(N): If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column va...

Completely OO C++ SQL Wrapper?

So I'm looking for a SQL wrapper for C++ that completely hides any textual SQL statements. I just can't seem to find any, I'm wondering why all the wrappers out there seem at some point to want you to write a textual SQL statement such as: SELECT * FROM stock WHERE item = 'Hotdog Buns' here's MySQL++ for example: mysqlpp::Query quer...

Error comparing dates in SQL query

Within a ASP.NET/C# class I am trying to run a query where I compare dates: select * from table1 CreatedDate >='DATEADD(d,-500,GETDATE())'; Basically I am trying to select rows from the the last 500 days. The problem is I am getting the following error: Syntax error converting datetime from character string. An example of the Creat...

How to do a JOIN in SQLAlchemy on 3 tables, where one of them is mapping between other two?

Suppose I have the following tables: Articles with fields article_id, title Tags with fields tag_id, name ArticleTags with fields article_id, tag_id And I wish to find all articles that have a given tag. How do I create this complicated join in SQLAlchemy? In SQL it would look like: SELECT a.article_id, a.title FROM Articles AS a J...

Recovery after wrong MySQL update query?

I made a wrong update query in my table. I forgot to make an id field in the WHERE clause. So that updated all my rows. How to recover that? I didn't have a backup.... ...