sql snippets

Example of how to do a for loop with PL/SQL

Tagged sql, pl/sql, oracle, for loop  Languages sql

A PL/SQL example of a for loop that prints out the primary key for all rows returned by a query:

DECLARE
  ID VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE (1000000);
    FOR current_row IN (SELECT id FROM your_table)
    LOOP
        ID := current_row.id;    -- Assign value to variable
        DBMS_OUTPUT.PUT_LINE( '' || ID);
        DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);
    END LOOP;
END;
/

Simplify and make SQL scripts run faster by using Oracle variables

Tagged sql, variables, oracle  Languages sql

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

COLUMN your_column new_value your_variable;

SELECT DISTINCT (your_column)
                                 FROM ...
                                WHERE ...;

SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

How to disable Oracle's variable name scanning

Tagged sql, oracle, disable  Languages sql

Normally Oracle will scan SQL for variables and prompt for the value at runtime, to disable this feature use the "set scan off" command:

set scan off;

select * from links where href = 'http://...&....';

How to select adjacent rows (next and previous rows) with MySQL

Tagged sql, mysql, next, previous, has_adjacent_finders  Languages ruby

I've now packaged this into a Rails plugin called has_adjacent_finders

Problem

Finding the next and previous product is a common task on, for example, e-commerce sites.

Let's say we have a table containing data having the following IDs:

201
202
203
204
205
206
207
208

How do we get the rows adjacent to row 205? We can rely on MySQL sorting—the primary key in this case— so these two queries will do the job for us:

# Find previous row
select id from products where id < 205 order by id desc limit 1

# Find next row
select id from products where id > 205 order by id asc limit 1

The two queries will return 204 and 206 respectively. You can also use other columns, not just ID...

Fix for "mysql error 1005 (errno: 150) "

Tagged mysql, sql, fix, problem, 150  Languages sql

There are at least three reasons, that I know of, to why you could be seeing mysql error 1005 (errno: 150)

  • Adding a foreign key constraint when column types don't match
  • Not enough privileges to execute the script
  • Trying to delete an index that is needed by some other index or constraint ("error on rename of")

Use "SHOW ENGINE INNODB STATUS;" to view the error:

ALTER TABLE videos ADD constraint fk_videos_channels_id FOREIGN KEY (channel_id) REFERENCES channels (id);
SHOW ENGINE INNODB STATUS;

How to update the ActiveRecord counter_cache magic column

Tagged update, counter_cache, sql, rails, active_record  Languages sql

You can use the model.update_counters method to update the counter_cache column. But if you have a million rows it be very fast.

So for large tables it's best to do it with a query such as this:

update categories, (select 
                      id as category_id, ifnull(count, 0) as count
                    from categories left join 
                      (select category_id, count(id) as count from products group by category_id) as count_table 
                    on 
                      categories.id = count_table.category_id) as count_table
set 
  categories.products_count = count_table.count
where
  categories.id = count_table.category_id

This query updates the count for all rows.

The code needs to be modified for your database design.

How to get the running total for data grouped by month for a MySQL query

Tagged running total, sql, mysql  Languages sql
SET @running_total:=0;
select 
    xx.*,
    (@running_total := @running_total + xx.total) AS running_total
from (
select count(id) total, concat(concat(year(created_at), '-', lpad(month(created_at), 2, '0'))) year_month
from users 
where created_at is not null group by year_month order by year_month
) as xx;

From http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql