sql

mysql multiple update

Hi, basically want to have a multiple update based on a few conditions. So this is my sql query which returns error: Table have 3 columns: user_id, week and change INSERT INTO rank (user_id, week) VALUES (364709193,'201042'),(291179703,'201042'),(394613472,'201042'),(284220417,'201042'),(395465205,'201042'),(394807905,'201042'),...

DB Design for Schedule

I'm creating a web application wherein users should be able to specify their weekly schedule. Here's my DB model for that: id user_id day from to day is an enum of 0-6 (0 for Sunday, 1 for Monday, and so on). from and to represent minutes from midnight. So, 0 means midnight, 1 means 12:01am, 2 means 12:02am, etc. Is this a good desig...

Query Idle Periods from Usage Information stored in SQL Database

I have a table in a PostgreSQL database which tracks usage of various resources. The (simplified) Schema of the table is that each row has a ResourceID, StartTime Timestamp and EndTime Timestamp. Each row in the table represents a timespan in which the resource was in use, so the table might look like: (Note, timestamps also include date...

Is there in-memory SQL database supporting replication/clustering?

Free and stable is the winner. My plan is quite trivial - just put all the data in memory and use the cluster without changing the application code. Then, for persistence, I could just dump the data from nodes in a regular disk-access database. The only thing is, that since I demand in-memory storage, there is no possibility of full da...

Updating a column with concatenated value

I have an id field (int type) and varchar field. I have to concatenate both columns and store the result to another column with data type nvarchar; Is this possible? ...

SQL Database Comparison

I have two databases: DBTarget and DBTest. I've run several custom scripts on DBTest, so now DBTest and DBTarget should be identical in every way (Tables, Values in tables, Columns, SPROCS, etc.) Question: Is there an easy way to compare these two databases? The only strategy I can think of is: USE [DBTarget]; SELECT * FROM tblTableN...

sql pivot with no aggregate

Whilst trying to pivot a sql table I came across this post Here . By using this method I have created a query. However i have now realised that it of course aggregates the results with the MAX function. However I need the Colum to pivot but for all occurrences to be show. Code taken from above post. SELECT dy, MAX(CASE WHE...

Introducing a new table between parent and child tables

If I have a parent and a child table filled with data, is it trivial to add a new table between them? For example, before introduction the relationship is: Parent -> Child Then: Parent -> New Table -> Child In this case I'm referring to SQLite3 so a Child in this schema has a Foreign Key which matches the Primary Key of the Parent T...

Using Java to populate a specific column in an SQL table

Essentially, I have a methods that manipulates data taken from a table to create a new object "ZExpert." ZExpert has parameters int id, int domain, and double ZExpert. I have added a column to the table I took the data from called "Z_Expert_Score." I want to put the double ZExpert score from the object in the column Z_Expert_Score, in ...

All possible combinations for two column data

I have a two column view Product Id Tag ---------------------- 1 Leather 1 Watch 2 Red 2 Necklace 2 Pearl I'm trying to get all possible combinations of tags for a product as such: 1 Leather 1 Leather,Watch 2 Pearl 2 Pearl,Necklace 2 ...

Deadlock help needed please

I have a peculiar situation. I have tables that are constantly accessed by different parts of our code and thousands of clients and so we employed the use of transactions when doing simple updates and inserts on our tables. Problem is we keep getting deadlock errors. Anyone have any idea how I can alleviate this problem? ...

SELECT * FROM tbl WHERE clm LIKE CONCAT('%',<other sql query LIMIT 1>,'%') - HOW???

How can I combine those two queries into one? 1) This finds the japanese sign for dog (犬): SELECT japanese FROM edict WHERE english LIKE 'dog' LIMIT 1; 2) This finds all japanese words with the sign for 'dog' (犬) in it: SELECT japanese FROM edict WHERE japanese LIKE '%犬%'; 3) I am having trouble combining those two i...

IRB and SQL syntax Question

I am trying to write this: post_view.id = 1 Comment.find(:all, :conditions => "post_parent_id = 'post_view.id'").size The second statement does not work because that is not an appropriate way to write post_view.id . Anyoone know the proper syntax? ...

Error 1046 No database Selected, how to resolve?

Error SQL query: -- -- Database: `work` -- -- -------------------------------------------------------- -- -- Table structure for table `administrators` -- CREATE TABLE IF NOT EXISTS `administrators` ( `user_id` varchar( 30 ) NOT NULL , `password` varchar( 30 ) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = latin1; MySQL said: #1046...

Strange Sql Server Query Slow

i have a query like this SELECT TOP 10 * FROM NEWS WHERE newsid > AAA ORDER BY newsid desc; this query is very slow for some values of AAA for example it is fast for 1,000,000 and 1,400,000 but it is slow for 1,355,316 I am confused!!! ...

SQL Server table-relations necessary with NHibernate?

Dear All! This may be a really silly question, but I've found no answer on google/bing... If I already use NHibernate for persistence with SQL Server, why should I then create all the table-relations on the database-schema? I'm just wondering because it seems to create all the relations, altough I already have defined them in the NHibe...

Increment field with not null and unique constraint in PostgreSQL 8.3

I have a table "items" with a column "position". position has a unique and not-null constraint. In order to insert a new row at position x I first try increment the positions of the subsequent items: UPDATE items SET position = position + 1 WHERE position >= x; This results in a unique constraint violation: ERROR: duplicate key valu...

quine (self-producing) SQL query

This is a very interesting wiki article about programs that print their own source code without any access to physical source file (in the filesystem). Examples in the articles include C and Scheme quine programs (yeah, it appears they are called like that). I remember someone asking me long ago (about 5 years) whether I could write an S...

Count distinct co-occurrences

I have a database with a listing of documents and the words within them. Each row represents a term. What I'm looking to do is to count how many documents a word occurs in. So, given the following: + doc + word + +-------+--------+ + a + foo + +-------+--------+ + a + foo + +-------+--------+ + a + bar + +-----...

Different query in the where section, how to transform into 1 query?

I have two simple queries select sum(deb)-sum(cre) as result1 from CXC where id='22731999' select sum(deb)-sum(cre) as result2 from CXC where id='22731999' and tipo='IM' the difference is the where, for example the first query results in 769686 and the second in 3469, what I have to do, to see the result of the two queries in one re...