RE: SQL problem |
||
  Epsilon Maj Pain in the Ass For a proper solution you'd have to say what database you use - otherwise its a bit useless to recommend things your DBMS might not even support. For example MS SQL could do that with T-SQL. PostgreSQL could do it with pivot ranges, etc. I'll assume its something forsaken as MySQL, which I can't be arsed to install, so here is a SQLite example of a SQL-only solution: gabb@IOTA:~$ sqlite3 test.db SQLite version 3.4.2 Enter ".help" for instructions sqlite> CREATE TABLE test ( id INTEGER, data TEXT ); sqlite> INSERT INTO test VALUES (1, "foo"); sqlite> INSERT INTO test VALUES (4, "bar"); sqlite> INSERT INTO test VALUES (9, "baz"); sqlite> SELECT * FROM test; 1|foo 4|bar 9|baz sqlite> CREATE TABLE fixed ( id INTEGER IDENTITY(1,1) PRIMARY KEY, data TEXT ); sqlite> INSERT INTO fixed (id) VALUES (1); sqlite> INSERT INTO fixed (id) SELECT id + (SELECT COUNT(*) FROM fixed) FROM fixed; sqlite> INSERT INTO fixed (id) SELECT id + (SELECT COUNT(*) FROM fixed) FROM fixed; sqlite> INSERT INTO fixed (id) SELECT id + (SELECT COUNT(*) FROM fixed) FROM fixed; sqlite> INSERT INTO fixed (id) SELECT id + (SELECT COUNT(*) FROM fixed) FROM fixed; sqlite> SELECT COUNT(*) FROM fixed; 16 sqlite> -- We repeated the INSERT statement 4 times -> 2^4 = 16 rows sqlite> -- If you need x rows, simple repeat it int(sqrt(x))+1 times sqlite> UPDATE fixed SET data = (SELECT data FROM test WHERE fixed.id = test.id); sqlite> SELECT * FROM fixed; 1|foo 2| 3| 4|bar 5| 6| 7| 8| 9|baz 10| 11| 12| 13| 14| 15| 16| sqlite> .quit gabb@IOTA:~$The last UPDATE-Statement was a correlated subquery, simply because SQLite doesn't support multi-table updates, MySQL does. So you could do instead in MySQL: UPDATE fixed JOIN test ON fixed.id = test.id SET fixed.data = test.data;A JOIN will be alot faster on larger tables, as its O(n+m) instead of O(n*n), but YMMV. If you want it nicer, pimp up the table declaration to have some meaningful default values and so on. THE, GABB Replies:
|
||
| CyberArmy::Forum v0.6 Generated In 0.00570 seconds |
2008-08-18 09:53:18
2008-08-18 12:02:45