Method 1: The method will display group wise duplicate leads. SELECT     eabhyasa_id, eabhyasa_name, eabhyasa_email, eabhyasa_mobile,stage_name,count(*) no_of_records FROM rns_leads as L     left join rns_stages as S on S.stg_id= E.eabhyasa_stg_id GROUP BY eabhyasa_email, eabhyasa_mobile HAVING count(*) > 1  Method 2: The below method will display all duplicate leads SELECT L.eabhyasa_id, L.eabhyasa_fname, L.eabhyasa_email, L.eabhyasa_mobile,S.stg_name, case when E.eabhyasa_cp_type=0 then "-" when E.eabhyasa_cp_type=1 then "Fssess" when E.eabhyasa_cp_type=2 then "full" when E.eabhyasa_cp_type=3 then "part1" when E.eabhyasa_cp_type=4 then "part2" when E.eabhyasa_cp_type=5 then "part3" when E.eabhyasa_cp_type=6 then "part4" end as Payment_Stage FROM rns_leads L left join gti_stages as S on S.stg_id= E.eabhyasa_stg_id INNER JOIN ( SELECT eabhyasa_email, eabhyasa_mobile,count(*) no_of_records FROM gt...

The below queries will remove all spaces, tabs characters, new line characters first and last space(s) from the table column. For replace all spaces :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '') For remove all tabs characters :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' ) For remove all new line characters :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '') http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace For remove first and last space(s) of column :  UPDATE `table` SET `col_name` = TRIM(`col_name`) http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

Some Common MySql Interview Questions and Answers For Experienced + Freshers Which mostly asked my interviewer during interview session, As you know MySql is highly popular relational database and good compatible with open source languages like PHP. Following list of basic Mysql question and answer surely help developers for getting new jobs. MySql Interview Questions and Answers Question: What is MySQL? MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.   Question: Why MySQL is used? MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet   Ques...

How to Change a MySQL Database’s Table Prefix: A Step-by-Step Guide Introduction Changing the table prefix of a MySQL database is a common task for enhancing security or reorganizing database structures. This guide walks you through the process step by step, ensuring clarity and efficiency. Whether you're a developer or a site administrator, these instructions will help you tackle the task with confidence. Why Change Your Table Prefix? Enhanced Security : Default table prefixes, like wp_ in WordPress, are often targeted by attackers. Changing them can reduce vulnerabilities. Customization : A new prefix makes your database easier to identify in complex environments. Avoid Conflicts : Helps prevent issues when importing databases into shared environments. Changing a database table prefix is easy and here’s the simple step-by-step guide! For WordPress installations, it’s essential! How to change a prefix 1. In your text editor, change database_name, old_prefix_ and new_prefix_ to ...

Run this code at regular intervals, for example once per day: Code: DELETE FROM moviesdone WHERE datetimecol < NOW() - INTERVAL 7 DAY

Method 1: SELECT count(*) total,     SUM(case when trim(last_name) like 'a%' then 1 else 0 end) A,     SUM(case when trim(last_name) like 'b%' then 1 else 0 end) B,     SUM(case when trim(last_name) like 'c%' then 1 else 0 end) C,     SUM(case when trim(last_name) like 'd%' then 1 else 0 end) D,     SUM(case when trim(last_name) like 'e%' then 1 else 0 end) E,     SUM(case when trim(last_name) like 'f%' then 1 else 0 end) F,     SUM(case when trim(last_name) like 'g%' then 1 else 0 end) G,     SUM(case when trim(last_name) like 'h%' then 1 else 0 end) H,     SUM(case when trim(last_name) like 'i%' then 1 else 0 end) I,     SUM(case when trim(last_name) like 'j%' then 1 else 0 end) J,     SUM(case when trim(last_name) like 'k%' then 1 else 0 end) K,     SUM(case when trim(last_name) like 'l%' then 1 else 0 end) L,     SUM(case when trim(last...

First, use  NOT REGEXP  instead of  !=1 . To allow  +  at the start optionally, use  ^\\+? . Since  +  is a special character in regular expressions, it must be escaped with a backslash (which must be escaped with another backslash). You then need one or more digits ( [0-9]+ ), up to the end of the string  $ . $query="UPDATE table SET phone='NULL' WHERE phone NOT REGEXP '^\+?[0-9]+$'"; This will match anything that doesn't begin optionally with  + , followed by digits only. If you also need to permit hyphens and dots in the phone number, add them into the  []  character class: '^\\+?[0-9.-]+$'

Mastering MySQL INTERVAL: Adding and Subtracting Time When working with databases, manipulating dates and times is a common task. Whether you need to find records from the last 30 days, calculate a future deadline, or analyze data within specific timeframes, MySQL's INTERVAL keyword is your best friend. The INTERVAL keyword is a powerful modifier used primarily with date and time functions like DATE_ADD() , DATE_SUB() , TIMESTAMPADD() , and TIMESTAMPDIFF() . It allows you to specify a duration or a period of time that you want to add to or subtract from a date or datetime value. The Basics: DATE_ADD() and DATE_SUB() with INTERVAL The most common use cases for INTERVAL are with the DATE_ADD() and DATE_SUB() functions. DATE_ADD(date, INTERVAL value unit) : Adds the specified value and unit to the date . DATE_SUB(date, INTERVAL value unit) : Subtracts the specified value and unit from the date . Syntax: SELECT DATE_ADD(date, INTERVAL value unit); SELECT DATE_SUB(date, INTER...

Unraveling MySQL's Aggregations: The Power of MAX() on COUNT() Are you looking to go beyond simple counts in your MySQL queries? Have you ever needed to identify the top performer, the most popular item, or the group with the highest number of occurrences? If so, you've likely bumped into the need to combine aggregate functions, specifically finding the MAX() value among a set of COUNT() results. While seemingly straightforward, directly applying MAX() to COUNT() in a single SELECT statement can be tricky due to how SQL processes aggregations. This post will demystify this powerful technique, showing you exactly why and how to correctly implement it using subqueries. The Challenge: Why SELECT MAX(COUNT()) Doesn't Directly Work Let's imagine you have an orders table and you want to find the customer_id that has placed the most orders. Your first thought might be: -- This won't work directly as intended! SELECT MAX(COUNT(order_id)) FROM orders GROUP BY customer_id; Th...

Mastering AUTO_INCREMENT in MySQL In the intricate dance of database management, the AUTO_INCREMENT attribute in MySQL plays a crucial role in automatically generating unique identifiers for your table rows. It's the silent architect of order, ensuring each new entry receives its distinct mark. However, there are times when the need arises to adjust or reset this seemingly straightforward mechanism. This post delves into the nuances of manipulating AUTO_INCREMENT, exploring the "why" and "how" with a touch of database finesse. Why Tinker with Automatic Increment? Understanding the Use Cases While AUTO_INCREMENT typically hums along seamlessly in the background, certain scenarios necessitate intervention: Data Purging and Clean Slate: After a significant data cleanup or during development phases, you might want to reset the counter to reflect a fresh start, especially if the IDs have become unusually high or fragmented. Testing and Reproducibility: For consiste...