MySQL query reference

To list all the databases;

show databases;

---

String to Date

SELECT dob, STR_TO_DATE(dob, "%d-%M-%y") FROM patdemofloridalab 

 

---

Query which contains future date.

SELECT * FROM patient WHERE dob >= CURDATE()

 

---

Example for Date_Sub

UPDATE patient SET dob = DATE_SUB(dob,INTERVAL 100 YEAR) WHERE dob >= CURDATE()

This will convert 01/01/2045 to 01/01/1945

---

Update mysql table with data from another table

UPDATE table1 t1, table2 t2
SET t1.field_to_change = t2.field_with_data
WHERE t1.field1 = t2.field2;

---

Add new column with default value

ALTER TABLE insurance ADD primaryedi INTEGER DEFAULT 1;

ALTER TABLE appsettings ADD claimstatusnew BIGINT NULL;

---

Drop Foreign Key constraint and Column

ALTER TABLE patientreceipt DROP FOREIGN KEY FK_patientreceipt_practiceID;
ALTER TABLE patientreceipt DROP   practiceID;

---

Update from another select

UPDATE tmpreport AS a
JOIN
(SELECT
    this_.accountCode AS accountcode,
        SUM(this_.billedAmount) AS amount
    FROM
        viewreporttranheader this_
    WHERE
        this_.DOSYear=2014 AND this_.DOSMonth = 3
    GROUP BY
        this_.accountCode) b
ON
  a.datacode = b.accountcode
SET billedAmt1 = b.amount