Monday, 19 May 2014

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

1 comment:

  1. Thanks for sharing pharma related information i am also sharing more about certified
    Pharmaceutical exporter & Manufacturer pharmacy company has always succeeded its goal to
    provides various services & medicine manufacturerspharmaceutical formulations company

    ReplyDelete