Warning: INSERT command denied to user 'dbo292345962'@'74.208.59.105' for table 'watchdog' query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', '%message in %file on line %line.', 'a:4:{s:6:\"%error\";s:12:\"user warning\";s:8:\"%message\";s:400:\"INSERT command denied to user 'dbo292345962'@'74.208.59.105' for table 'captcha_sessions'\nquery: INSERT into captcha_sessions (uid, sid, ip_address, timestamp, form_id, solution, status, attempts) VALUES (0, '888a7d2f822c18f1eebe82ab2c6c04a5', '54.209.18.224', 1545222968, 'comment_form', 'ef357b90cdf4e2775b996671cde01b37', 0, 0)\";s:5:\"%file\";s:62:\"/homepages/25/d199835659/htdocs/ID/modules/captcha/captcha.inc\";s:5:\"%line\";i:99;}& in /homepages/25/d199835659/htdocs/ID/includes/database.mysql.inc on line 135
Top 5 Handy Tricks using SQL Queries | Inferno Development

Top 5 Handy Tricks using SQL Queries

database SQL

Once in a while, as a developer, you'll see a cool new SQL statement that might blow your mind or question yourself on how you never came up with this. Such a magnificent SQL statement might have saved you hours of time. I wanted to go over some awesome tricks and tips in SQL that might make your database management easier.

These tricks are very vital for developers working with enterprise databases with large amounts of legacy data.

I mainly use MySQL, MSSQL, PostgreSQL, but many of these commands should work similarly in other databases as well.

#1 - Update one table's data to match a second table's data based on a common field

Table 1 employees:

id name since
211 Bob 2009
212 George 2008
213 Tyrion 2005
214 Christina 2011
.... more data

Table 2 employees_new

id name jobtitle oldid since
341 Bob consultant NULL NULL
342 Josh developer NULL NULL
343 Tyrion Manager NULL NULL
344 Christina developer NULL NULL
.... more data

In this example, you have a table that you've always used to display information, but a new large table of updated information is available, but the ids are all wrong and need to match up.

You can combine these tables like this:

UPDATE employees_new
SET employees_new.id = eold.id, employees_new.since = eold.since
FROM employees_new enew
INNER JOIN employees eold
ON eold.name LIKE enew.name
WHERE enew.id > 20

This statement replaces all the id and since fields in the new table with ones from the old data, based on matching names, as well as making sure we only include employees with id greater than 20 (because our first 20 employees are already updated).

Table 2 employees_new RESULT:

id name jobtitle oldid since
341 Bob consultant 211 2009
342 Josh developer 294 NULL
343 Tyrion Manager 212 2005
344 Christina developer 213 2011

So yes, you can use FROM and INNER JOIN in UPDATE statements.

You can use this method in DELETE statements too.

DELETE mytable FROM mytable TN INNER JOIN JoinTable JN ON JN.col1=TN.col2 WHERE JN.col3 > 10

#2 - Get all column names in a database and create an SQL statement

So let's say you have a database table that has 50 fields or maybe 100 fields.

But let's say you have to update them all, or check if any of the fields are null or something? Well this trick will allow you to write a great SQL statement.

The following example is in a very MSSQL specific format:

DECLARE @tb NVARCHAR(255) = N'dbo.[table]';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

This will for example, check your table "table" to see if any of the columns have a NULL value in it then select it.

Alternatively you can change:

EXEC sp_executesql @sql;

To

SELECT @sql;

And now it will display the actual query generated.

SELECT * FROM dbo.[employees_new] WHERE 1 = 0 OR [id] IS NULL OR [name] IS NULL OR [jobtitle] IS NULL OR [oldid] IS NULL OR [since] IS NULL

#3 - Subqueries

These subquery SQL statements can be very helpful in certain specific situations.

SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

This allows you to select multiple values that are a possibility for one value in the first table.

#4 - The IN() function

The IN function can be quite useful, as it allows you to check a list of possible values that could be used in a column.

SELECT * FROM citizens WHERE Nation IN ('United States', 'Germany', 'France', 'United Kingdom', 'Norway', 'Sweden', 'Turkey');

#5 - The LIKE clause

If you had a table with a lot of values then you can search for specific words that are contained within it.

SELECT * FROM countries WHERE Nation LIKE '%United%'

Would return:

'United Kingdom', 'United States', 'United Arab Emirates'

Alternatively "LIKE 'United%'" would have worked too. The % sign being the wild-cards, so any characters can be after United.

Also "LIKE '%United'" would mean any countries that END with "United", which would return zero.

exor's picture

Yeah the IN() function is a

Yeah the IN() function is a time saver alright.

feer's picture

Update queries with joins

Update queries with joins really do help fix a lot of databases.

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account associated with the e-mail address you provide, it will be used to display your avatar.