MySQL Create Table Syntax Example

Quick one:

CREATE TABLE mytable
(
myid INT NOT NULL AUTO_INCREMENT,
unique_name VARCHAR(255),
full_name VARCHAR(255),
phone_number VARCHAR(255),
loyalty_points INT,
PRIMARY KEY(myid,full_name)
);

2 Column (or multiple columns) Primary Key in Sqlite

To create a table with 2 (or multiple/more) column primary keys in sqlite:

CREATE TABLE table1 (column1 INTEGER, column2 INTEGER, column3 VARCHAR(255), PRIMARY KEY (column1, column2));

Updating a table in SQL where records in table A don't exist in table B.

I find myself doing this a lot recently, so I'm posting it here too. Hopefully it is SQL92 and should run on just about anything.


1. Updating a table in SQL where records in table A don't exist in table B:

insert into tableB (field1, field2, field3)
select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like '%bleh%'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2);



2. Hey, and why not do something silly like have a union query and some limit in the mix (limit works in MySQL only):

insert into tableB (field1, field2, field3)
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A1
union
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 not like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A2;

MySQL, Insert Statement "ERROR 1064 (42000): You have an error in your SQL syntax"

So, I got the error "ERROR 1064 (42000): You have an error in your SQL syntax" when trying to execute a similar insert statement to:

INSERT INTO sections (code, desc) VALUES('123','example of a description');

It was due to the 'desc' field being a reserved word. If you're getting a similar error, make sure that your field names are not using any reserved words in MySQL. Although, yes it is kind of strange that MySQL would allow you to create fields based on reserved words to begin with. To solve my problem, I used:

INSERT INTO sections (code, description) VALUES('123','example of a description');