I would like to update and count in a single query.  My table structure is:  |----|-------|------------| | id | views | update     | |----|-------|------------| |  1 |     1 | 2019-05-30 | |----|-------|------------|   The desired output is:  |----|-------|------------| | id | views | update     | |----|-------|------------| |  1 |     2 | 2019-05-31 | |----|-------|------------|   My try is the following:  UPDATE table  SET views = (     SELECT COUNT(views)      FROM table      WHERE id='1'  ) + 1, update='2019-05-31' WHERE id='1';   I'm tried to update the view count by adding +1 to the actual count and update the update field with the new date.  But it doesn't work.  What I'm doing wrong ?  Thanks.
3320
I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:  SELECT *  FROM  story_category  WHERE category_id NOT IN (     SELECT DISTINCT category.id      FROM category INNER JOIN         story_category ON category_id=category.id);   I tried to delete them executing:  DELETE FROM story_category  WHERE category_id NOT IN (     SELECT DISTINCT category.id      FROM category        INNER JOIN story_category ON category_id=category.id);   But I get the next error:      #1093 - You can't specify target table 'story_category' for update in FROM clause    How can I overcome this?
71555
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.  I have this query:  SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` group by matchid, ordinal having dacount &gt; 1 order by dacount desc   which correctly gives me the ids of the rows I need to delete. The problem with this though is that I can't use this as a subquery on the DELETE because of the dacount with the having parameter. Is there another way to do this?  Here was my plan:  DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` GROUP BY matchid, ordinal HAVING dacount &gt; 1)
120454
I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.   This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was committed and the flight booked.  I'm new to T-SQL, and not sure on how to use @@rowcount. This is what I've written until now. Am I on the right road? I'm sure is an easy problem for you.  -- BEGIN TRANSACTION (HOW TO DO?)  UPDATE Bookings  SET TicketsBooked = TicketsBooked + @TicketsToBook  WHERE FlightId = @Id AND TicketsMax &lt; (TicketsBooked + @TicketsToBook)  -- Here I need to insert only if the row doesn't exists. -- If the row exists but the condition TicketsMax is violated, I must not insert  -- the row and return FALSE  IF @@ROWCOUNT = 0  BEGIN   INSERT INTO Bookings ... (omitted)  END  -- END TRANSACTION (HOW TO DO?)  -- Return TRUE (How to do?)
98617
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.  How things start:  SELECT COUNT(fullpath) FROM data;  12395   I use the column fullpath a lot so I create an index:  CREATE INDEX "index" ON data (fullpath);   I then add the column:  ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);   Insert values into the Sub column based on the fullpath:  insert into data(Sub) select case     when fullpath like '%subA%' then 'subA'     when fullpath like '%subB%' then 'subB'     else 'neither' end from data;   Check the number of rows again:  SELECT COUNT(fullpath) FROM data;  24790   I don't understand the syntax and the operations. Do I need to join or something to respect the index?
21299
I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?  Real state:   id       name     position -------  -------  ------- 1        S4       null 2        S2       null 3        S3       null 4        S1       null     Expected:   id       name     position -------  -------  ------- 1        S4       4 2        S2       2 3        S3       3 4        S1       1   I tried with update:  WITH x AS (   SELECT       id,       name,       ROW_NUMBER() OVER (ORDER BY name) position   FROM       stage ) UPDATE     stage SET     position = x.position FROM     x WHERE     x.id = stage.id;   Stage is the name of the table.
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?
49893
Found the following in an Oracle-based application that we're migrating (generalized):  SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2   What does (+) do in a WHERE clause? I've never seen it used like that before.
52243
I would like to update  to add some new information. (I realized that when "walking the clustered index" of a table that has a multi-column clustered index, in most cases a great simplification can be achieved by using WITH TIES in the update statement to enable working only with the first column of the clustered index. I'd also like to change a couple of other minor things.)  However, if I make this update, I'll hit 10 updates, and the answer will get converted to community wiki. I hate asking moderators to remove community wiki status--it seems like a waste of their time. At the same time, it bugs me that my answer is not as good as it could be! And yes, it bugs me to lose the reputation if it goes community wiki.  Note that I am doing my best to make more substantial edits and fewer trivial ones, so I don't run into this limit as often. But it does happen sometimes. You can look at the answer history to see if you think that my 8 edits were excessive or not.  Should I:   Avoid making the edit, Make the edit in pursuit of having the best answer and just take my lumps so I don't inconvenience moderators, Or make the edit and flag for a moderator as it's not that big a deal for them?
35042
This might be a stupid question but is there a way to update records (yes many records and duplicate ones) in a DE without a primary key? I am not the owner of the DE which is already structured for several years so I cannot change its fields.  Thanks.
57131
I've found  many queries to check edit counts, and I get different results for each one. Which one is the most accurate and up-to-date query? Or is there any way to see the edit count in stats?
64714
I have the following table:  NAME Alex Bob  Bob Tim Alex Roger   I need to produce the following:   Name    Count Percentage  Alex    2     33%  Bob     2     33%  Roger   1     16.6%  Tim     1     16.6%   I have no idea where to start.  I can do the initial query for the count which is simple.
42544
I am given the schema: EMPLOYEE(Fname,Ssn,Dno) DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) PROJECT(Pname,Pnumber,Plocation,Dno) WORKS_ON(Essn,Pno) fiddle:  Primary keys are given in bold. The question: Is it possible to define a view that will report the following detail for each employee: Ssn and the number of employees who work on exactly the same set of projects? Explain why not or given an SQL query. My idea was to find unique pairs of employees and check if projects worked by first employee equal those of the second one (this was done by using set operation except as if s1 except s2 union s2 except s1 is empty than it seems like s1 and s2 are equal) My attempt for this was: SELECT e1.ssn,count(e2.ssn) FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.ssn &lt; e2.ssn AND NOT EXISTS (  (  (SELECT Pno  FROM WORKS_ON w1  WHERE w1.Essn = e1.Ssn)  EXCEPT ALL  (SELECT Pno  FROM WORKS_ON w2  WHERE w2.Essn = e2.Ssn)  )   UNION ALL    (  (SELECT Pno  FROM WORKS_ON w2  WHERE w2.Essn = e2.Ssn)  EXCEPT ALL  (SELECT Pno  FROM WORKS_ON w1  WHERE w1.Essn = e1.Ssn)  ) ) GROUP BY(e1.ssn)  Does this query look correct? Also is there a neater way of doing this? As a final side note what exactly determines if it is impossible for us to construct an SQL query for a problem?
86274
Here's a scenario:  Someone asked about "How to build a SQL query to get count and blah blah blah..."  His only tag is . I added the  and  tags to it.  Someone else answered with a nice SQL statement that has a "group by" statement which seems "logically inevitable".  I proceed to add a tag . I did that on the basis that someone who searches for  will be able to find this post useful, just as I did. My concern is, it wasn't described at all in the question, nor was it implied, unlike  and .  What is the community opinion?  The scenario above is a real one . And the one who answered it nicely is Jon Skeet.
87139
Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?  I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.
11639
In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.  What is the best way to do that?  Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
49890
I'm currently trying to create a View from a Table that does the following:  Table A ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║    FOO,BAR    ║ ║  PN0004-01  ║  BAR,FOO,FOB  ║ ╚═════════════╩═══════════════╝   To create  View B ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║      FOO      ║ ║  PN0003-01  ║      BAR      ║ ║  PN0004-01  ║      BAR      ║ ║  PN0004-01  ║      FOO      ║ ║  PN0004-01  ║      FOB      ║ ╚═════════════╩═══════════════╝   So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.