Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

4
votes
2answers
6k views

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 ...
7
votes
1answer
179 views

IN and NOT IN for XML column

I have a table with a xml column. Xml is similar to <Root> <Row> <user>abc</user> <Rowid>1</Rowid> </Row> <Row> ...
-1
votes
1answer
62 views

Database update rather than cycle through? [closed]

I'm new when it comes to MySQLi and databases so I'm not sure how exactly to title this question. I have a PHP script setup to where it inserts data into the database. Pretty self explanatory. ...
7
votes
1answer
5k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
2
votes
2answers
306 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
0
votes
0answers
27 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities? [duplicate]

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
-3
votes
1answer
1k views

How to merge two rows in a table? [closed]

I have a table called Person with columns Col1, Col2, Col3, etc. In Col1 I have two values: ABC and XYZ. What I want is to replicate all the values from the other columns (Co2, Col3, etc.) for the ...
0
votes
1answer
146 views

Foreign key in MySQL

I am learning about database, I need to know something about the foreign key in MySQL. Consider the following two tables. UserGroupType. UserGroup. UserGroupType: UserGroup: In this table ...
4
votes
3answers
3k views

SQL Server 2008 - performing nightly full backups & transaction log shipping, but log file keeps growing

Rather perplexed with my SQL server Log file. With identical primary & secondary servers, the production server on live sends the log files to the secondary every 15 minutes. Also, every evening, ...
1
vote
2answers
91 views

Practical way to temporarily shut down a database

What would be the most practical way to shut down a database to access for any user? I need to practically shut down a specific database on a SQL Server for test purposes, and would like to know the ...
3
votes
1answer
193 views

Most efficient ordering post database design

I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, ...
0
votes
1answer
105 views

How to optimize query?

I need to optimize the following query: SELECT DISTINCT column1, column2 FROM tbl_name WHERE column3 = 'x' AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) AND TRIM(UPPER(column5)) = ...
2
votes
3answers
199 views

Performance difference between searching table with 350 columns and searching table with 25 columns

I am currently doing some hibernate performance test. My requirement is to search for data in a particular column of a table containing 8 million rows and 350 columns. Currently, I don't have the ...
2
votes
1answer
385 views

Polling for new records in a table

We have a table that is append+read only. We'd like to have multiple applications consume all new rows that show up in that database, in a cheap and easy way (without change tracking). The simple ...
4
votes
1answer
765 views

Negative number of rows inserted

I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message: -1,311,750,183 rows inserted Should I be concerned about the fact that it's negative?
1
vote
1answer
463 views

Inserting filtered data from one table to another

I need to find the number of employees who are not in all departments. I have one more table T2 in which same department column is present. Now I want to insert only those values of department which ...
0
votes
1answer
543 views

Need to find the number of employee who are not in all department

i have a table T1 which has two columns empname , department every emp must be in 37 department , but there are some employee who are in less than 37 department . I need to find the names of emp who ...
4
votes
4answers
20k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
2
votes
1answer
5k views

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value' I have 2 tables... Table 1 is called accounts Table 2 is called ...
-1
votes
1answer
62 views

What could I possibly be doing wrong with this update statement

UPDATE pcdbak SET pcdbak.Title = pcd.Title FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum This was the error message: [Err] 1064 - You have an error in your ...
2
votes
2answers
91 views

Get rows in a range of time

I have a table with two columns that stores TIME type values. This is the CREATE statement: CREATE TABLE `sdriving_turno` ( `idturno` bigint(20) NOT NULL AUTO_INCREMENT, `idempresa` int(10) ...
1
vote
1answer
111 views

Unable to generate a MySQL query

I have a table in a MySQL database as below: ID Timestamp State 1 2013-07-10 09:00:00 Good 1 2013-07-10 09:10:00 Good 1 2013-07-10 09:20:00 Good 1 2013-07-10 09:30:00 Bad 1 ...
1
vote
1answer
51 views

Getting related data from an uncertain column

I am working on a friendship logic, the table friendship has this structure: `friendship_id` INT NOT NULL , `user1_id` INT NOT NULL , `user2_id` INT NOT NULL , `status` INT NULL , `created` ...
6
votes
1answer
1k views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
23
votes
2answers
7k views

Check existence with EXISTS outperform COUNT! … Not?

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. Yet in several recent scenarios I've measured a performance improvement when using ...
2
votes
4answers
1k views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
6
votes
1answer
223 views

Database restructure - beneficial?

I have a table for email messages. Then, I have a table that has the message parts. The Parts table contains a field_id, message_id, and data At the time I used Parts to name the table and ...
2
votes
2answers
13k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
8
votes
1answer
1k views

IO_STALL question and understanding

I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO. In one 5min period I get a delta of 5826331 ms ...
0
votes
1answer
381 views

RTF to Plain-Text? [closed]

Did you ever get the library from this post? We have a need to convert whole, large, whole tables worth of RTF information into something usable and would rather not reinvent if possible. Converting ...
3
votes
0answers
230 views

How can I search values, considering occurences, on more than one term in SQL Server CE?

I have a rather complex SQL query, that I actually received help from this SE site to come up with parts of. This query is designed to take a search term and search all relevant values in a database ...
3
votes
1answer
518 views

Cumulative Game Score SQL

I have developed a game recently and the database is running on MSSQL. Here is my database structure Table : Player PlayerID uniqueIdentifier (PK) PlayerName nvarchar Table : GameResult ID ...
4
votes
4answers
14k views

Difference between Full backup and Copy-only full backup

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup? For the log backup there is copy-only backup which prevent the ...
-6
votes
2answers
216 views

Inserting images in database [closed]

How can the images be inserted in a table? Var binary is not working in structured query language command prompt,what can be the syntax?
2
votes
2answers
187 views

MySQL #1064 error on line 2 [closed]

I'm quite new on learning SQL and I seem to be running into an issue with the following code: CREATE TABLE `read` (`order` NOT NULL UNIQUE AUTO_INCREMENT, `title` VARCHAR(50) NOT NULL, `url` ...
4
votes
1answer
319 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
1
vote
1answer
877 views

Alter table move taking long time

I'm currently trying to compress a table in Oracle with the following statements: ALTER TABLE MYTABLE COMPRESS FOR OLTP; ALTER TABLE MOVE; My question is: the MOVE operation is taking forever and ...
1
vote
2answers
1k views

Obvious inefficiencies in this Netezza query?

Why might this query be taking nearly 2 hours to run when similar queries complete within a minute? CREATE TABLE T3 AS SELECT A.*, COALESCE(C.FIELD1,0) AS FIELD1A, ...
1
vote
2answers
311 views

Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria

I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think ...
1
vote
1answer
120 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
-8
votes
1answer
9k views

Using inner join to get data from multiple tables [closed]

I have 5 tables: A (a_id, a1, a2) B (b_id, b1, b2, a_id) C (c_id, c1, c2, b_id) D (d_id, d1, b_id) E (e_id, e1, e2, d_id) Here is some information table b have a_id match with table a (a_id). ...
4
votes
1answer
503 views

Splitting a table based on enum value

I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in ...
3
votes
3answers
13k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
-4
votes
0answers
111 views

Multi-row INSERT rejected? [closed]

What's wrong with my query? :( USE tempdb; IF OBJECT_ID('dbo.OrderTotals', 'V') IS NOT NULL DROP VIEW dbo.OrderTotals; IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails; ...
2
votes
1answer
1k views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
2
votes
1answer
121 views

Zero conversion in sql

Char convertion in Oracle have a pattern: to_char(number,'pattern') and provide option "s" to take sign to result, for ex: select to_char(10,'S999') from dual; will return +10, but when i check select ...
5
votes
2answers
346 views

One table used in two databases

I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different ...
0
votes
1answer
237 views

Update column value in entire database [closed]

In my database, I have around 30 tables, each with an emp_number (INT) column. How can I update the value of emp_number in all 30 tables?
-1
votes
1answer
101 views

How do I create a trigger that cascades new rows into another table?

I am having a table name link with two columns link1 and link2 and another table name hide with four columns hide1, link1,link2 and hide2. If i insert data into link table the data should insert into ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...