Updating in mysql
Suppose I want to update a table with data from a subquery that refers to the same table.
I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using non-standard syntax, and so on.
Here’s a contrived example: create table apples(variety char(10) primary key, price int); insert into apples values('fuji', 5), ('gala', 6); update apples set price = (select price from apples where variety = 'gala') where variety = 'fuji';.
The My SQL manual mentions this at the bottom of the UPDATE documentation: “Currently, you cannot update a table and select from the same table in a subquery.” It’s pretty easy to work around the problem in this contrived example, but there are times when it’s not possible to write the query without subqueries that refer to the update target. Since My SQL materializes subqueries in the clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery.
If you enjoyed this article, subscribe via feeds or e-mail to receive my articles regularly.
Update 2006-08-29 The queries I’ve given here are sloppy, performance-wise.
Please let me know what you suggest, and thanks in advance.
The DB user in your file isn't super user on the DB server. but if you were to use CLI and the mysql client you would use mysql -u root (root being superuser ... Editing does NOT require restart or reboot of server, but you should be aware that any one hitting the site with super user crendtials in the fle will have highest prevls with the DB. safe to do cause the CLI scripts normally don't take but a few minutes at the most ...
Don’t rely on properly formed queries to populate these columns! CREATE TABLE orders( id INT NOT NULL AUTO_INCREMENT, user_id INT NULL, amount FLOAT NULL, updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(), created_at TIMESTAMP NOT NULL, PRIMARY KEY(id)); Query OK, 0 rows affected (0.00 sec)So now your table has been created.