We have a table (let us call it originalTbl) that has duplicate entries that we want to delete. By duplicate I mean all values other than an AUTO INCREMENT index field are the same. One way to do this is to create a new table, like the existing table (let us call it uniqueTbl), and then have a query like:
Tag: mysql
How to Sort this MySQL Query
I am trying to insert the ORDER BY in this query to sort the Total column: With the following result: Here’s what I have tried so far but no luck: Answer I think you want: Rationale: the ORDER BY clause should go after all UNION ALL subqueries – so it needs to be outside of the GROUP_CONCAT(), in …
Auto-increment a primary key in MySql
During the creation of tables using mysql on phpmyadmin, I always find an issue when it comes to primary keys and their auto-increments. When I insert lines into my table. The auto_increment works perfectly adding a value of 1 to each primary key on each new line. But when I delete a line for example a line w…
MySQL:Trying to run CUBE query -> Error in your sql syntax | LIMIT 0,25
I have query in my structure trying run in PHPMYADMIN. I am trying to run CUBE query for OLAP operation, this is my query : I have also tried this query : but it showing this error : I tried to open the page to check syntax but it shows page not found. Answer I don’t think MySQL supports CUBE
is a LOCK TABLE needed for `UPDATE tbl SET col = col + 1`?
lets say that col is 0, and 100 clients is trying to increase it by 1 at the exact same time, is the LOCK TABLE needed here? or can i just do and be sure that col becomes 100? (eg that mariadb makes sure none of them reads the same value twice~) i’m basically trying to do the equivalent of
How to understand the execution sequence of SELECT and ORDER BY
I googled the question and all answers said SELECT was executed before ORDER BY. But the simple example below (using MySQL and sakila database) gives the correct sorted results. Obviously, ORDER BY is not executed after SELECT, as customer_id is not selected by SELECT. Can anybody explain what happened? SELEC…
MySQL query for multi-column distinct plus an ancillary column condition
Imagine a flat table that tracks game matches in which each game has three participants: an attacker, a defender and a bettor who is wagering on the outcome of the battle between players 1 and 2. The table includes the names of the players and the bettor of each game, as well as the date of the game, the scor…
Calculate distance and only get one result for every trail_part_id
What I want to achieve is to get only the closest result for every trail_part_id. However I am stuck, I tried to use GROUP BY trail_part_id, but that did not work either as I got the following error message: SELECT list is not in GROUP BY clause and contains nonaggregated column ‘p.latpoint’ which…
For all entries in one table, add to another table
Database looks like this: table1 table2 I want to make a query that passes the same data for all ID’s into table2. So after the query the database would look like the following: table1 table2 I’ve tried to make some INNER JOIN queries, but I can’t seem to make it work. Any suggestions? Answe…
MySQL – Recursively list all parents and ancestors of all items in table
I have a table with a parent/child hierarchy that supports multiple (theoretically infinite) levels of nesting: I am trying to build a query that produces a concatenated list of every item’s parent items up until the highest parent in the tree: Based on a number of other answers here I have constructed …