Correlated subquery in an update statement in MySQL

--CELKO-- in comp.databases wrote:
> The problem of maintianing a sequence number has come up a few times.
....
> UPDATE Flix
> SET seq = (SELECT COUNT(*)
> FROM Flix AS F1
> WHERE F1.seq <= Flix.seq);

On Mon, 02 May 2005 14:23:37 -0700, "C.W.Holeman II" in comp.databases
wrote:

>Then I changed less and used MySQL:
>
>mysql> DROP TABLE Flix;
> CREATE TABLE Flix
> (seq INTEGER NOT NULL,film CHAR(5) NOT NULL);
>Query OK, 0 rows affected (0.00 sec)
>Query OK, 0 rows affected (0.00 sec)
>mysql> INSERT INTO Flix VALUES
> (1, 'a'),(2, 'b'),(3, 'c'),(4, 'd'),(5, 'e'),(6, 'f');
>Query OK, 6 rows affected (0.00 sec)
>Records: 6 Duplicates: 0 Warnings: 0
>mysql> SELECT * FROM Flix;
>+-----+------+
>| seq | film |
>+-----+------+
>| 1 | a |
>| 2 | b |
>| 3 | c |
>| 4 | d |
>| 5 | e |
>| 6 | f |
>+-----+------+
>6 rows in set (0.00 sec)
>mysql> UPDATE Flix
> SET seq = (SELECT COUNT(*)
> FROM Flix AS F1
> WHERE F1.seq <= Flix.seq);
>ERROR 1064: You have an error in your SQL syntax. Check the manual that
>corresponds to your MySQL server version for the right syntax to use near
>'SELECT COUNT(*) FROM Flix AS F1 WHERE F1.seq <= Flix.seq)' at l

Lemming wrote in comp.databases wrote:
> (Caveat: I've never used a correlated subquery in an update statement,
> I've always done such things procedurally).
>
> The original query has the table included twice; once in the UPDATE
> and once in the subquery. In the update it's named Flix, in the
> subquery it's named (aliased as) F1. The query says "for every row on
> Flix, set it's sequence number to the count of rows which have a
> sequence number lower or equal to than the current one".
>
> So if table Flix contains:
>
> Seq Film
> 01 Movie1
> 03 Movie2
> 04 Movie3
>
> Then 01 will remain as 01, 03 will become 02, 04 will become 03.

--CELKO-- in comp.databases wrote:
>Well, you are NOT using SQL; MySQL is nothing like real SQL and you are
>going to get really messed up if this is your first database language.--

So, how do I handle this:

UPDATE Flix
SET seq = (SELECT COUNT(*)
FROM Flix AS F1
WHERE F1.seq <= Flix.seq);

in MySQL?

--
C.W.Holeman II
cwh5ii@Julian5Locals.com remove the fives

Re: Correlated subquery in an update statement in MySQL

C.W.Holeman II wrote:
> --CELKO-- in comp.databases wrote:
>
>>The problem of maintianing a sequence number has come up a few times.
>
> ...
>
>>UPDATE Flix
>> SET seq = (SELECT COUNT(*)
>> FROM Flix AS F1
>> WHERE F1.seq <= Flix.seq);
>
>
> On Mon, 02 May 2005 14:23:37 -0700, "C.W.Holeman II" in comp.databases
> wrote:
>
>
>>Then I changed less and used MySQL:
>>
>>mysql> DROP TABLE Flix;
>> CREATE TABLE Flix
>> (seq INTEGER NOT NULL,film CHAR(5) NOT NULL);
>>Query OK, 0 rows affected (0.00 sec)
>>Query OK, 0 rows affected (0.00 sec)
>>mysql> INSERT INTO Flix VALUES
>> (1, 'a'),(2, 'b'),(3, 'c'),(4, 'd'),(5, 'e'),(6, 'f');
>>Query OK, 6 rows affected (0.00 sec)
>>Records: 6 Duplicates: 0 Warnings: 0
>>mysql> SELECT * FROM Flix;
>>+-----+------+
>>| seq | film |
>>+-----+------+
>>| 1 | a |
>>| 2 | b |
>>| 3 | c |
>>| 4 | d |
>>| 5 | e |
>>| 6 | f |
>>+-----+------+
>>6 rows in set (0.00 sec)
>>mysql> UPDATE Flix
>> SET seq = (SELECT COUNT(*)
>> FROM Flix AS F1
>> WHERE F1.seq <= Flix.seq);
>>ERROR 1064: You have an error in your SQL syntax. Check the manual that
>>corresponds to your MySQL server version for the right syntax to use near
>>'SELECT COUNT(*) FROM Flix AS F1 WHERE F1.seq <= Flix.seq)' at l
>
>
> Lemming wrote in comp.databases wrote:
>
>>(Caveat: I've never used a correlated subquery in an update statement,
>>I've always done such things procedurally).
>>
>>The original query has the table included twice; once in the UPDATE
>>and once in the subquery. In the update it's named Flix, in the
>>subquery it's named (aliased as) F1. The query says "for every row on
>>Flix, set it's sequence number to the count of rows which have a
>>sequence number lower or equal to than the current one".
>>
>>So if table Flix contains:
>>
>>Seq Film
>>01 Movie1
>>03 Movie2
>>04 Movie3
>>
>>Then 01 will remain as 01, 03 will become 02, 04 will become 03.
>
>
> --CELKO-- in comp.databases wrote:
>
>>Well, you are NOT using SQL; MySQL is nothing like real SQL and you are
>>going to get really messed up if this is your first database language.--
>
>
> So, how do I handle this:
>
> UPDATE Flix
> SET seq = (SELECT COUNT(*)
> FROM Flix AS F1
> WHERE F1.seq <= Flix.seq);
>
> in MySQL?
>

I wouldn't, I'd use something better ;-)

But here's a try:

CREATE TEMPORARY TABLE nwseq AS
SELECT f1.seq, COUNT(f2.seq) AS nwseq
FROM Flix f1, Flix f2
WHERE f2.seq <= f1.seq
GROUP BY f1.seq;

And then:

UPDATE Flix f1, nwseq n
SET f1.seq = n.nwseq
WHERE f1.seq = n.seq;

I've tried it with your testdata and it worked for me.

Cheers

Re: Correlated subquery in an update statement in MySQL

Brommer wrote:
> C.W.Holeman II wrote:
>> --CELKO-- in comp.databases wrote:
>>
>>>The problem of maintianing a sequence number has come up a few times...
>>>UPDATE Flix
>>> SET seq = (SELECT COUNT(*)
>>> FROM Flix AS F1
>>> WHERE F1.seq <= Flix.seq);
>>
>> So, how do I handle this:
>> in MySQL?
>>
> But here's a try:
>
> CREATE TEMPORARY TABLE nwseq AS
> SELECT f1.seq, COUNT(f2.seq) AS nwseq
> FROM Flix f1, Flix f2
> WHERE f2.seq <= f1.seq
> GROUP BY f1.seq;
>
> And then:
>
> UPDATE Flix f1, nwseq n
> SET f1.seq = n.nwseq
> WHERE f1.seq = n.seq;

All of that useful information has given me enough to created an example
that is a bit more complete. It operates on a sequences with holes and
accepts user modifications.

Original sequence with user specified changes shown:

+-----+------+---------+
| Seq | Film | UserSeq |
+-----+------+---------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 5 | e | 2 | User changes "e" from "5" to "2".
| 6 | f | 6 |
+-----+------+---------+

Seq is the order of the table before the user action. User changes "e" to a
"2" which is to mean that "e" is to be placed just after #2 "b" and just
before #3 "c". On the other hand, if the user wanted "e" to be before #2
"b" then "1" would be entered instead of "2" for "e".

Sequence after updated with the user specified changes:

+-----+------+---------+
| Seq | Film | UserSeq |
+-----+------+---------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | e | 2 |
| 4 | c | 3 |
| 5 | f | 6 |
+-----+------+---------+

The following is intended to update the flix table to have the Seq values
in the order specified by the user with UserSeq. The order is first based
upon the numbers as entered [or left unchanged] by the user with the
sub-ordering based on the original order. Any blanks in the sequence also
fill in.

DROP TABLE flix;
CREATE TABLE flix
(Seq INTEGER NOT NULL,
Film CHAR(5) NOT NULL,
UserSeq INTEGER NOT NULL);
INSERT INTO flix
VALUES (1,'a',1),(2,'b',2),(3,'c',3),(5,'e',2),(6,'f',6);
SELECT * FROM flix ORDER BY Seq;
+-----+------+---------+
| Seq | Film | UserSeq |
+-----+------+---------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 5 | e | 2 |
| 6 | f | 6 |
+-----+------+---------+

DROP TABLE us;
CREATE TABLE us AS
SELECT Film, flix.UserSeq*1000+flix.Seq AS Seq
FROM flix
ORDER BY Seq;
SELECT * FROM us;
+------+------+
| Film | Seq |
+------+------+
| a | 1001 |
| b | 2002 |
| e | 2005 |
| c | 3003 |
| f | 6006 |
+------+------+

DROP TABLE ns;
CREATE TABLE ns AS
SELECT u1.film, u1.Seq, COUNT(u2.Seq) AS NewSeq
FROM us u1, us u2
WHERE u2.Seq <= u1.Seq
GROUP BY u1.Seq;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

SELECT * FROM ns;
+------+------+--------+
| film | Seq | NewSeq |
+------+------+--------+
| a | 1001 | 1 |
| b | 2002 | 2 |
| e | 2005 | 3 |
| c | 3003 | 4 |
| f | 6006 | 5 |
+------+------+--------+

UPDATE flix, ns
SET flix.Seq = ns.NewSeq
WHERE flix.Film = ns.Film;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 5 Changed: 3 Warnings: 0

SELECT * FROM flix ORDER BY Seq;
+-----+------+---------+
| Seq | Film | UserSeq |
+-----+------+---------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | e | 2 |
| 4 | c | 3 |
| 5 | f | 6 |
+-----+------+---------+

--
C.W.Holeman II
cwhii@Julian5Locals5.com remove the fives
http://free.ProHosting.com/cwhii