DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
2answers
999 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
166 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
111 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 ...
-7
votes
1answer
6k 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); table b have a_id match with table a(a_id). table c have b_id match ...
3
votes
1answer
391 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
9k 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
92 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
824 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
116 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
282 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
173 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
75 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
3k 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 ...
1
vote
0answers
562 views

Creating view recursive query in Oracle DB results in ORA-00600 (ORA-600)

Hello Ladies and Gents, I've been trying to deploy a recursive query as a view in Oracle XE and Standard Edition, neither to much success. The query is located in this question here: ...
2
votes
2answers
254 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
1answer
502 views

Does this error message indicate a SQL injection vulnerability?

I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible? Microsoft OLE DB Provider ...
1
vote
2answers
165 views

Is there any way to tell the number of matches found in a database value matched with LIKE?

Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)? EXAMPLE ...
4
votes
2answers
721 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
7
votes
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
2
votes
1answer
11k views

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
7
votes
2answers
1k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
0
votes
1answer
62 views

Wrong return results

I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
1
vote
2answers
469 views

Is it possible to build an UNDO framework for postgres?

I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the ...
-3
votes
1answer
142 views

What will the sql statement “DELETE FROM sales” do?

DELETE FROM sales; And if there are no other uncommitted transactions on the sales table. Which statement is true about the DELETE statement? It removes all the rows in the table and deleted rows ...
-1
votes
1answer
113 views

No predicate warning [closed]

I am getting a No predicate warning on this query. Can someone help? SELECT DISTINCT TOP 100 T0.nsid, T0.id, T0.version, ...
0
votes
1answer
356 views

How to profile a particularly slow or inefficient query

I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries. ...
0
votes
0answers
51 views

Any recommendation on tuning following query

My query has lot of select statements and unions. I think it is querying database multiple times and combining data. Is there a way we can eliminate multiple passes to the database? I would like to ...
-1
votes
2answers
10k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...
1
vote
1answer
239 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
0
votes
1answer
405 views

xml queries with exist and contains giving unexpected results

I have a query as shown below. DECLARE @tbl TABLE ( id INT, col XML ) INSERT INTO @tbl VALUES (1,'<Root> <Row> <User>xyz</User> ...
0
votes
1answer
109 views

Chaining System Query in MySQL

I have a table which maintains a user chain Here is the table structure +------+--------+ | id | user | +------+--------+ | 2 | 4 | | 10 | 4 | | 11 | 4 | | 1 | 2 | ...
2
votes
2answers
9k views

Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this: WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME, GRANTED_ROLE ...
1
vote
2answers
1k views

Query for master in Postgres replication

I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication. From a slave, I can: SELECT pg_is_in_recovery() And ...
8
votes
5answers
25k views

Error 3154 while restoring a backup using WITH REPLACE

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak. I have a database with the name test2 which is the same database, but the data changed. I want to restore ...
0
votes
3answers
2k views

Space used by databases file through linked server

I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers). I created view on client size, for all databases: ...
0
votes
2answers
939 views

SQL Server Maintenance Plan TSQL query to log the backup databases

Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up? Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3. ...
1
vote
1answer
806 views

Count number of common column names among all tables in a database

Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over ...
4
votes
1answer
1k views

ORDER BY optimization for a large table

I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest. Consider a table posts posts { id ...
2
votes
2answers
3k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
2
votes
1answer
145 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
0
votes
2answers
5k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
2
votes
3answers
35k views

How to write a query to find all tables in a db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like: select ...
3
votes
3answers
4k views

Nonclustered index is faster than clustered index?

Both tables have same structure and 19972 rows in each table. for practicing indexing, i created both tables having same structure and created clustered index on persontb(BusinessEntityID) and ...
0
votes
1answer
86 views

Hazards of Upgrading SQL Server Express 2008 to R2

A client of mine reached the 4GB database limit on a SQL Server 2008 Express. As a test I installed the R2 version on another machine and restored a backup of the database. I'd like to upgrade the ...
0
votes
1answer
547 views

Simplify subqueries with join

I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data. ...
0
votes
1answer
241 views

how important are mysql's innodb logs?

I'm basically concerned about the following two settings, these are my default settings : innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 I haven't changed them for 2 reasons : one, ...
5
votes
2answers
307 views

Given two known statements, how can I replicate deadlock?

I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
5
votes
1answer
4k views

Need to divide single column into multiple columns

My table has data with a single column: 1 2 3 4 5 6 7 8 9 10 And I want output like this: 1 2 3 4 5-first row 6 7 8 9 10 - second row Another has two columns like this: Column 1 ...
1
vote
1answer
419 views

Column of generic/variable type?

In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is: CREATE TABLE TagValue ( tag VARCHAR(64), ...
-1
votes
1answer
342 views

How to select a row on one distinct attribute? [closed]

I want to select * from A A but only where A.attr1 is distinct. How can I do this?