New answers tagged

0

SELECT * FROM animals WHERE FIND_IN_SET(name,(SELECT @animal_names)); See the FIND_IN_SET documentation


0

Build a Stored Routine; in it use CONCAT to construct the SELECT with the @variable stitched in; prepare and execute. Alternatively, if you are using an application language such as PHP, do the stitching in that language.


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 ...


0

You could do: SELECT * FROM animals WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%'); It won't perform fast, but it'll work.


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 ...


0

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

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 ...


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 ...


0

Here is what I did: CREATE TABLE junk.ja_jobs_WM ( recurrenceid integer, time_job int8, time_job_stamp timestamp, dupcount integer, most_recent_date timestamp ); INSERT INTO junk.ja_jobs_WM SELECT recurrenceid, time_job, TO_TIMESTAMP(time_job), ...


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 = ...


0

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )


2

Query 1 Show 1 if the latest ID row in the database has scan date of today otherwise show 0. Can be written more simply (and more efficiently): SELECT CASE WHEN scan.date = CURDATE() THEN 1 ELSE 0 END AS datecheck FROM scan ORDER BY scan.id DESC LIMIT 1 ; and (if we like obfuscation): SELECT (scan.date = CURDATE()) AS datecheck FROM scan ORDER BY ...


4

Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert: INSERT INTO scan (id, date) SELECT (MAX(id)+1), CURDATE() FROM scan HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0; To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)


0

As Jkavalik commented, the LIKE query with a starting % [could not be indexed](http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning ). LIKE filters can only use the characters before the first wild card during tree traversal. The remaining characters are just filter predicates that do not narrow the scanned index ...


2

First get rowid of the row and object id of the object using DBMS_ROWID. SQL> insert into t3 values('S00102981655537O',sysdate,'IN-RJ'); 1 row created. SQL> select dbms_rowid.rowid_object(rowid) from t3 where doc_id='S00102981655537O'; DBMS_ROWID.ROWID_OBJECT(ROWID) ------------------------------ 93178 SQL> select subobject_name ...



Top 50 recent answers are included