User Comments

Ever wanted to turn an AUTO_INCRIMENT primary key into one of those 'rolling ID' columns? i.e. the type which changes back to ID = 1 when some other part of your (new) PK changes... Use a subquery!

Suppose you have this

TABLE t1...

AUTO_INCR_PK <-> X
1 <-> A
2 <-> A
3 <-> A
4 <-> B
5 <-> B
6 <-> B
7 <-> C
8 <-> C
9 <-> D

Try this cool sub-query!

CREATE TABLE t2 ( ID, X, PK(ID,X) );

INSERT INTO t2;
SELECT
a.X
a.AUTO_INCR_PK -
b.FIRST_KEY_IN_SERIES AS ID
FROM
t1
INNER JOIN
(
SELECT
X,
MIN(AUTO_INCR_PK) AS FIRST_KEY_IN_SERIES
FROM
t1
GROUP BY
X
) AS b
USING
(X)
;

Which gives you

TABLE t2 ...

ID <-> X
1 <-> A
2 <-> A
3 <-> A
1 <-> B
2 <-> B
3 <-> B
1 <-> C
2 <-> C
1 <-> D

Cool eh?

If you can't use subquery, you can use this;

$sec1 = mysql_query("SELECT foto FROM profoto WHERE proje=$id");
if ($kyt1 = mysql_fetch_array($sec1)) {$dizi = $kyt1["foto"];} else {$dizi="0";}
while ($kyt1 = mysql_fetch_array($sec1)) {
$dizi = $dizi . "," . $kyt1["foto"];
}
mysql_free_result($sec1);
$sec = mysql_query("SELECT foto.id, foto.dosya, foto.baslik FROM foto WHERE id NOT IN (" . $dizi . ") LIMIT $baslangic, $sayfalama");

First, you create a selection and then you use it in your real selection. This is a kind of subquery :)

This order of things also works for count functions. For example:

SELECT *,(SELECT COUNT(*) FROM table2 WHERE table2.field1 = table1.id) AS count FROM table1 WHERE table1.field1 = 'value'

This command will enable you to count fields in table2 based on a column value in table1 and label the result as "count". The value in table1.field1 can be any valid field type.

Here's simple insert query I came up with that serves 3 functions in my PHP applications:
1.) retrieves data from the select query
2.) Inserts that row into another table (for record keeping purposes such as purchase orders from shopping carts)
3.) validates the input (using mysql_affected_rows()) and will return 0 if the select failed and will not insert at all.

INSERT INTO table2 (field1, field2, field3, field4) (SELECT 'value1 from user input', field1, field2, field3 from table1)

The 4 fields in table2 will be populated by the 4 fields (including the string) returned by the SELECT sub-query respectively.

I know this MIGHT raise issues with speed of queries but it's better than writing long lines of PHP code that does those 3 things - even with a framework! I can just use the mysql_affected_rows() after that query to see if everything went fine.

NOTE: Make sure the number of fields in the SELECT query is EXACTLY the same number of fields you are about to insert.

It mentions here that you cannot select from and modify the same table as part of your subquery. There is a workaround listed on the following page:

http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html

that shows you how to use a temporary table. But you can also create a View based on a table, use that for the SELECT statement and then use the regular table name for the UPDATE / DELETE statement.

Updating a table using a subquery. This uses an example of a table of people, and a separate table of votes those people have received. After the votes table has been populated with new votes, the total vote count of each of the people is calculated and updated with one query.

UPDATE people,
(SELECT count(*) as votecount, person_id
FROM votes GROUP BY person_id) as tally
SET people.votecount = tally.votecount
WHERE people.person_id = tally.person_id

Combine queries for Insert and Select always obeys Innodb locking rules
if one of the source table is based on Innodb engine.
It is also possible that the INSERT activity applicable to TEMPORARY
table which is not InnoDB engine. It is also possible that in SELECT
section with INNODB, some other TEMPORARY Tables are used.
Devang Modi

Add your own comment.