Category: sql

Categories
MySQL sql

INSERT IGNORE MySQL

The INSERT MySQL statement allows you to add multiple rows into a table but should an error occur, MySQL terminates and returns an error. This means no row is inserted into the table. When you need to ignore the error and allow insertion of valid data the INSERT IGNORE statement is one you can use. MySQL will throw a warning but the valid data will be inserted into the table.

We will create a table members with a unique mobile column, this will be our constraint ensuring we don’t duplicate mobile numbers.

CREATE TABLE members (INT PRIMARY KEY AUTO_INCREMENT, mobile VARCHAR(13) NOT NULL UNIQUE
);

Now we insert a new row:

INSERT INTO members(mobile) VALUES('+254711111111');

This works fine, now lets try inserting three rows into the table

INSERT INTO members(mobile) VALUES('+254711111123'), ('+254711111112'), ('+254711111111');

Continue reading “INSERT IGNORE MySQL”
Categories
sql

Generate random records for quick SQL learning

Here is a scenario; you want to run SQL queries on a large dataset but you do not this size of data in a database for you to be able to run your queries, the reason for wanting to do could be purely for learning something new or test on fake data. Luckily database systems have some form of random function to let us do this. The following SQL queries will help illustrate this:

First I will create a database called learnings and connect to this database

root=# CREATE DATABASE learnings; 
root=# \c learnings;

I will create the item table with name and price columns

Continue reading “Generate random records for quick SQL learning”
Categories
scripting sql

What will be the date x days from now?

Today I learn some nifty shell command. The command simply answers the question, what will be date x days from now. So lets says I wanted to know what will be date and time 13 days from now

echo "select now() + '30 days'::interval" | psql

As you can see from this I am piping into psql (postgresql terminal frontend) so this is simply a SQL statement run from the shell.