Hi all,
Just wondering with an UPDATE query, what is the real function of the SORT BY parameter.
It's just that I have a number of rows in a table that are keyed to a row in another table and an array of the new values to be updated.
The SORT BY function would be really handy if I could send the values to a single query and let it update in the order they need to be. Otherwise I will need to run individual updates for each row, and I'm usure of how to keep them in order.
As an example, I have products in tbl_products, the available sizes in tbl_sizes, and the prices in tbl_prices. The entry in products defines the product, and the sizes defines the different sizes for all items sorted per category from smallest to largest, and the tbl_prices entry links a product to a size and defines a price.
I then have a form which pulls out the price values for all sizes of a particular product, and when I push submit, I want to change the prices for the appropriate rows in the prices table.
If I could pass all the values to a single update with a SORT BY setting, then maybe I could update them in the correct order, otherwise I'm not sure how I can ensure that the correct value goes in the correct place short of querying the list of prices again and then looping though the ID's as targets for update.
Any pointers on best method would be great. Cheers.
Update query question
Moderator: General Moderators
afaik the update query doesn't have a sort by clause. (or what sql dialect are we talking here?)
right now i think you need a serious crash course on sql... and relational databases in general.... other people that concurrently query the database won't see the in-between results from your query.....
right now i think you need a serious crash course on sql... and relational databases in general.... other people that concurrently query the database won't see the in-between results from your query.....
Sorry ... this is for a mySQL database.
I'm sort of working off a vague reference in the mySQL manual.
I'm interested to know what your solution would be, given my need for more mysql knowledge. I think the table structure is perfect, frankly. It's the only structure I can find that has the level of flexibility I require.
Each item is a one of, but each item requires seperate prices for an unknown number of sizes which can apply to multiple items.
The problem comes when I go to edit the values, hence the question regarding different update methods.
Any information would be appreciated. Cheers again.
I'm sort of working off a vague reference in the mySQL manual.
I dont know why, but it just strikes me that the sort wouldn't really have much of a purpose unless different values were to be issued, although now that I come to write it, perhaps it would be used to add an incrementing value to a list in a specific order.Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
The UPDATE statement updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.
I'm interested to know what your solution would be, given my need for more mysql knowledge. I think the table structure is perfect, frankly. It's the only structure I can find that has the level of flexibility I require.
Each item is a one of, but each item requires seperate prices for an unknown number of sizes which can apply to multiple items.
The problem comes when I go to edit the values, hence the question regarding different update methods.
Any information would be appreciated. Cheers again.
Good stuff, thanks for that.
Looking into transactions with mySQL, but I'm not sure about using innoDB. Dont know if my host uses it, whether or not they will, or how much (if any) a performance hit the database will take.
Looking into it, it seems the way to go, especially in terms of error handling. If part of the routine doesnt work, it undoes all previous steps.
Any opinions or info about inno would be good. Searching around anyhow.
Cheers
Looking into transactions with mySQL, but I'm not sure about using innoDB. Dont know if my host uses it, whether or not they will, or how much (if any) a performance hit the database will take.
Looking into it, it seems the way to go, especially in terms of error handling. If part of the routine doesnt work, it undoes all previous steps.
Any opinions or info about inno would be good. Searching around anyhow.
Cheers