MySQL UPDATE Statement The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax: UPDATE tablename SET col1 = val1, col2 = value2, … WHERE condition; Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records … Read more
MySQL where condition WHERE Syntax SELECT column1, column2, … FROM table_name WHERE condition; Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.! SELECT * FROM Customers WHERE Country=’Mexico’; Operators in The WHERE Clause The following operators can be used in the WHERE clause: Operator Description = Equal <> Not equal. Note: In … Read more
Very often we need to extract last 30 days, 7 days data from mysql DB. Here we will show easy way to get the data of given interval. SELECT DATE_FORMAT(create_date, ‘%m/%d/%Y’) FROM mytable WHERE create_date BETWEEN CURDATE() – INTERVAL 30 DAY AND CURDATE() Also note that CURDATE() returns only the DATE portion of the date, so … Read more
Very often we need to extract last 1 month, 2 months data from mysql DB. Here we will show easy way to get the data of given interval. SELECT * from testtable WHERE startdate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND DATE_SUB(NOW(), INTERVAL 2 MONTH) The above query will return records whose order date is between one and … Read more
Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error … Read more
Some of the optimizations performed by MySQL on where clauses as follow: Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) To (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 To b>5 AND b=c AND … Read more