Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with subquery
Search options questions only
not deleted
user 3787
SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.
1
vote
0
answers
95
views
How to populate a 'map' table from words of a text?
Having the database structure that follows:
CREATE TABLE articles
(
article_id int(11) unsigned NOT NULL AUTO_INCREMENT,
article text,
PRIMARY KEY(article_id)
) ENGINE=InnoDB;
CREATE TABLE words
(
w …
- 4,531
1
vote
1
answer
497
views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 1 …
- 4,531
0
votes
1
answer
1k
views
How to update a table by count from another table?
FROM entries WHERE list LIKE '%United Kingdom%',
ca = (SELECT COUNT(*) FROM entries WHERE list LIKE '%Canada%'
WHERE id = 'xx'
How can I reduce the number of subqueries, and count all values in one subquery …
- 4,531
2
votes
1
answer
3k
views
How to UPDATE a column by COUNT of FK from another table?
How to extend this query for the entire table, to update all ids?
UPDATE table1 SET number=(
SELECT COUNT(*) FROM table2 where id=1
) WHERE id=1
id is the PRIMARY KEY in table1 and FK in table2.
H …
- 4,531
2
votes
1
answer
5k
views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM …
- 4,531
1
vote
1
answer
35
views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.Tag …
- 4,531
0
votes
2
answers
90
views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY ( …
- 4,531
0
votes
1
answer
2k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
HAVING …
- 4,531
0
votes
1
answer
31
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID A …
- 4,531
3
votes
1
answer
28k
views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B.id …
- 4,531