SQL

Technical Notes

These notes are my personal online notebook of useful commands and "how-to's". You are welcome to make use of them if you find them helpful. They obviously don't come with any warranty! Click on one of the category tags above for the notes in any category.

Some examples of MySQL Syntax

These are here purely to jog my own memory as to how I did things. Probably not much use to anyone else
--2008-02-06
--added new fields: mast_contacts.firm_num, mast_contacts.no_office, mast_firm.dup_branch
--added new indexes on: mast_firm.master_id_tag, mast_firm.office_id (UNIQUE)

SELECT * FROM mast_contacts c JOIN mast_firm f ON f.office_id = c.office_id
WHERE firm_num is null ORDER BY f.office_id;

UPDATE mast_contacts c JOIN mast_firm f ON f.office_id = c.office_id
SET c.firm_num = f.mast_firm_num
WHERE dup_branch= -1 AND c.city = f.city;

MySQL mysqlimport examples

see: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
and also: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

mysqlimport [options] db_name textfile1 [textfile2 ...]

cd "C:\Program Files\MySQL\MySQL Server 5.0\bin"
mysql -u root -p -e "CREATE TABLE temp_email(name VARCHAR(200), email VARCHAR(50) PRIMARY KEY)" cfaw
mysqlimport -u root -p --lines-terminated-by="\r\n" cfaw c:\temp\temp_email.tsv

Campaignmonitor.com subscribers can be downloaded as TSVs from the subscriber page
-needs separate downloads for bounces and unsubs

Syndicate content