Hot answers tagged

3

My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result. Something like this will get you on your way: SELECT name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent, MAX(CASE WHEN ...


3

You could use some kind of row generation technique, for example: select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet) This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows. SELECT * FROM animals WHERE name IN(SELECT @animal_names); is translated to SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\''); MySQL variable cannot hold table resultset so ...


2

Try the following: UPDATE tests AS t JOIN testusers AS tu ON t.TestNumber= tu.TestNumber SET t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot)) WHERE tu.UserId=25 AND tu.UserSlot >= 0;


1

MySQL doesn't have syntax for UPDATE ... FROM, however it does allow for UPDATE table1, table2,... SET table1.col = value WHERE table1.id = table2.id. You can try the following: UPDATE Tests AS t, (SELECT TestNumber, UserSlot FROM TestUsers WHERE UserId=25 AND UserSlot >= 0) AS tu SET t.InactiveTestSlotBitwise = ...


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...


1

The concatenation is occurring before the addition causing it to fail trying to add a string to a number. Concatenation and addition both have the same level of operator precedence, but are processed from left to right, so if you had removed the leading concatenation, the trailing one would succeed. As Balazs explained, adding parenthesis fixes the order ...


1

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...



Only top voted, non community-wiki answers of a minimum length are eligible