Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [derived-tables]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
5 votes
1 answer

Merge Delete on joined tables is allowed but has a bug

To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table. But they ...
Charlieface's user avatar
  • 13.3k
1 vote
1 answer

Is it possible to reduce the duplication in this simple query?

I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string ...
user225326's user avatar
0 votes
1 answer

MySQL query - Passing variable into subquery's derived table

Is there a way to pass a variable into a subquery, and use that variable in a derived table in the subquery? I am trying identify "active" organizations, and there are multiple actions that ...
Jared Anderton's user avatar
0 votes
2 answers

Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)

I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ...
user2431082's user avatar
0 votes
1 answer

mysql query with 1 derived table and 1 inner join takes forever to load

I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
usc_student's user avatar
1 vote
3 answers

Improve Query Performance of Select Statment inside IN Statment

My query is: (3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in ( (2)SELECT post_id FROM ( (1) ...
HOY's user avatar
  • 121
0 votes
1 answer

SQL query does not recognise derived table

So I've made a simple SQL query: SELECT companyname FROM works w1, ( SELECT companyname,sum(salary) AS sumsal FROM works GROUP BY companyname ...
mrmagin's user avatar
3 votes
1 answer

Delete Joined to Derived Table Causes Index Scan and Table Spool

Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates. ...
Bryan Rebok's user avatar
  • 1,169
13 votes
4 answers

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
Vlad Mihalcea's user avatar
9 votes
5 answers

Difference between inline view and WITH clause?

Inline views allow you to select from a subquery as if it were a different table: SELECT * FROM /* Selecting from a query instead of table */ ( SELECT c1 FROM ...
Kshitiz Sharma's user avatar
0 votes
2 answers

Update statement using Case statement, derived table, and ROW_NUMBER

Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their ...
saoco's user avatar
  • 3
2 votes
1 answer

mysql group by on table from two derived non-indexable

Working with mysql 5.7. I've got a query that ends up joining two derived tables and grouping on one of the columns. The query without the grouping runs pretty fast.... .5 seconds and returns 15K ...
Josh's user avatar
  • 159
2 votes
0 answers

How does the use of derived tables speed up sorting?

In the article 45 Ways To Speed Up Firebird Database, it states that Use derived tables to optimize SELECT with ORDER BY/GROUP BY Another way to optimize SQL query with sorting is to use ...
oals's user avatar
  • 121
1 vote
0 answers

Get unique rows based on max of a column in mysql

I came across many solutions like the one in this question. However, they all are restricted to data from one table. I'm very very new to SQL. I'd like to get the same results as in the linked ...
abn's user avatar
  • 111
0 votes
2 answers

How are derived table implemented in MySQL

When MySQL makes a derived table as a result of SELECT (SELECT ...) etc or SELECT * FROM a JOIN (SELECT * from B) etc or SELECT * FROM (SELECT ...) Are these derived temporary tables created in-...
Cratylus's user avatar
  • 1,013
8 votes
2 answers

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
norgematos's user avatar