I want to decrement the column value by 1 before finding that value in array.  select *  from tbl_credit  where int_credit in (12, 13, 14, 15)  int_credit=13   But I want to decrement int_credit by 1 as like below  select *  from tbl_credit  where 12 in (12, 13, 14, 15)   Can I implement the logic in this query?
120999
I'm wanting to decrement a variable in a MySQL table by one everytime an UPDATE query is ran.  What I have is this, which isn't working:  UPDATE forum SET replys = reply--  WHERE fid = '$id'  Is this possible in any way, or am I going to have to run a SELECT and get the value first, decrement it, and then insert the new value into the UPDATE query?
33666
I am running a query like   select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';   on table that looks like                                      Table "public.students"           Column       |            Type             |             Modifiers                   -------------------+-----------------------------+------------------------------------      id                | uuid                        | not null default gen_random_uuid()      school_id        | uuid                        |  Indexes:     "students_pkey" PRIMARY KEY, btree (id)     "students_school_id_idx" btree (school_id)   The query plan for the select statement with just where looks like below-  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';                                             QUERY PLAN                                             --------------------------------------------------------------------------------------------------  Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)    Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)    -&gt;  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)          Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   This is fairly fast.  Now we add order by to the query with id that degrades the query.(Such a query is generated by Rails like student.first with some condition)  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                  QUERY PLAN                                                  ------------------------------------------------------------------------------------------------------------  Limit  (cost=0.43..488.51 rows=1 width=16)    -&gt;  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)          Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   How can I improve the speed to return the results of this query? Currently there are around 4990731 records in the table and is taking more than 2 minutes! Its running on RDS with db.t2.medium instance.  UPDATE After running Analyze students;  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                        QUERY PLAN                                                         -----------------------------------------------------------------------------------------------------------------      Limit  (cost=8.46..8.46 rows=1 width=16)        -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16)              Sort Key: id              -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)                    Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)      explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                                           QUERY PLAN                                                                              -----------------------------------------------------------------------------------------------------------------------------------------------------------     Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)      -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)            Sort Key: id            Sort Method: quicksort  Memory: 25kB            -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)                  Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)     Planning time: 0.145 ms     Execution time: 1.874 ms
112694
What mysql functions are there (if any) to trim leading zeros from an alphanumeric text field?    Field with value "00345ABC" would need to return "345ABC".
115109
Consider this table in mysql  create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers;  +--------+ | number | +--------+ |      3 | |      2 | |      9 | +--------+   Is there a simple query to generate a table with the following columns   The numbers from 1 till 10 1 if the number is present in the table numbers, and 0 otherwise   I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.  Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?
96092
I have three columns as shown below. What is the best way to pull all the values from column 3 that are not the same in the other columns? the result should return 2, 7 and 9 as they are only in column 3.   Column 1 |  Column 2  | Column 3 ---------|------------|--------- 1        |    5       |     1 3        |    4       |     2 1        |    5       |     7 4        |    6       |     9   I tried  not in (Column 1, Column 2) method but it checks by row only and I need it to take first value in column 3 and compare against the whole column in 1 and 2....
107704
I have data like multiples of 40. 40 80 120 160 200 and so on.    I would like to label every record that is a multiple of 100.  I have tried a couple of methods and can't get them to work either as a query in the label or as a select statement.  "STATION" =  %00   RIGHT ("STATION", 2) =  00     I get invalid SQL statement. Does anyone see what I am doing wrong?    Respectfully I did search and did not see any of the example questions you presented.  However it still does not answer my question, re-stated, what am I doing wrong?
48061
How can ALTER be used to drop a column in a MySQL table if that column exists?   I know I can use ALTER TABLE my_table DROP COLUMN my_column, but that will throw an error if my_column does not exist. Is there alternative syntax for dropping the column conditionally?  I'm using MySQL version 4.0.18.
4620
I am trying to use a SQL expression in order to select values that are >= 25% of its max value in that column. For instance, VALUE &gt;= MAX(VALUE)*0.25, and those will ultimately be the selected points it pulls out.   Is there syntax to make that happen, or am I going to need to create a new column and use field calculator on it?   Biggest issue with that is I will have to do this for about 250 files. I am also trying to create this in ModelBuilder.  When I try it as above it just returns that there is an error with the expression.
31743
I am converting some module from d5 to d7, and in old code there is an update query like this:  &lt;?php db_query("UPDATE {a_table} SET count = count + 1 WHERE field1 = %d", $some_value);   As we see, it is incrementing a field value (AFAIK it is a mysql feature). But, if I use new style of update it won't work.  &lt;?php db_update('a_table')     -&gt;fields(array('count' =&gt; 'count + 1',))     -&gt;condition('field1', $some_value)     -&gt;execute();   Are there any workaround within new style of db update without running an extra query to get existing field value to increment it?
115369
I am trying to accomplish the following in MySQL (see pseudo code)  SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) &gt; 10 ORDER BY lastupdated DESC   Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.
107777
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:  SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)   I want to select data where two columns are in a set of pairs.  I'd like to avoid using a subquery, if possible.
11491
I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:  ALTER TABLE tablename AUTO_INCREMENT = 1   I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?  Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!
6834
Given a number, how do I discover in what table and column it could be found within? I don't care if it's fast, it just needs to work.
77617
When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?
94031
I have a query in a before-update trigger that looks like following:  [SELECT Id,(SELECT Id,Active_opp__c              FROM Opportunities              ORDER BY Days_from_Last_Stage__c ASC)   FROM Account WHERE Id IN: conAccMap.values()]   I have checked this in query plan and it looks like :    Is there a chance that the inner query will cause selective query error since I dont have a WHERE Clause on an indexed field in the inner query?
57377
Working on a project at the moment and we have to implement soft deletion for the majority of users (user roles). We decided to add an is_deleted='0' field on each table in the database and set it to '1' if particular user roles hit a delete button on a specific record.  For future maintenance now, each SELECT query will need to ensure they do not include records where is_deleted='1'.  Is there a better solution for implementing soft deletion?  Update: I should also note that we have an Audit database that tracks changes (field, old value, new value, time, user, ip) to all tables/fields within the Application database.
22320
I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need.  If I need to SELECT every column in a table, should I use   SELECT * FROM TABLE   or   SELECT column1, colum2, column3, etc. FROM TABLE   Does the efficiency really matter in this case? I'd think SELECT * would be more optimal internally if you really need all of the data, but I'm saying this with no real understanding of database.  I'm curious to know what the best practice is in this case.  UPDATE: I probably should specify that the only situation where I would really want to do a SELECT * is when I'm selecting data from one table where I know all columns will always need to be retrieved, even when new columns are added.   Given the responses I've seen however, this still seems like a bad idea and SELECT * should never be used for a lot more technical reasons that I ever though about.