Quote of the Day: No comment

Free alcohol for MPs was introduced in the late 1990s to prevent deputies from going to bars during debates.

EUobserver reporting that the Belgian parliament has rejected advice from an independent integrity committee to ban the distribution of free beer and wine to MPs.

Flattr this!

Merging data with SQL MERGE

There have been times, many times, when I have needed to update the one or more columns in a table with values retrieved from another. My usual approach to this is to use an UPDATE with subselects but it turns out that there is a better way:

The MERGE statement updates a target (a table or view) using data from a source (result of a table reference). Rows in the target that match the input data may be updated or deleted as specified, and rows that do not exist in the target may be inserted as specified. Updating, deleting, or inserting a row in a view updates, deletes, or inserts the row into the tables on which the view is based if no INSTEAD OF trigger is defined on the view.

In order to play around with this, I build and populated a couple of test tables (imaginatively named TEST01 and TEST02) using the below scripts:

create table test01
 (key_field char(1) not null,
  data01 char(20),
  data02 char(30),
  data03 char(5),
  primary key (key_field));

insert into test01
 (Key_field, data01)
values('A', 'TEXT One');

insert into test01
 (Key_field, data01)
values('B', 'TEXT Two');

insert into test01
 (Key_field, data01)
values('D', 'TEXT Four');
create table test02
 (key_field char(1) not null,
  data01 char(20),
  data02 char(30),
  data03 char(5),
  primary key (key_field));

insert into test02
 (Key_field, data02)
values('A', 'More text one');

insert into test02
 (Key_field, data02, data03)
values('C', 'more text two', 'CODE');

insert into test02
 (Key_field, data01, data02)
values('E', 'TEXT five', 'More text five');

This gives me two tables with one matched row (key_field=’A’). To update the first table with the values in the second is really rather simple:

MERGE INTO test01 a
  USING (SELECT key_field, data01, data02, data03 from test02) b
  ON b.key_field = a.key_field
WHEN MATCHED THEN
   UPDATE SET data02 = b.data02, data03 = b.data03;

And the one matched row is updated in table TEST01 with the data from table TEST02.

If this was all I could do, I would be happy to have replaced a rather clunky approach with a more readable solution. But now the MERGE statement really starts to make a difference:

MERGE INTO test01 a
  USING (SELECT key_field, data01, data02, data03 from test02) b
  ON b.key_field = a.key_field
WHEN MATCHED THEN
   UPDATE SET data02 = b.data02, data03 = b.data03
WHEN NOT MATCHED THEN
   INSERT (key_field, data01, data02, data03)
   VALUES (b.key_field, b.data01, b.data02, b.data03)
NOT ATOMIC
  CONTINUE ON SQLEXCEPTION;

The WHEN NOT MATCHED clause means that not only can I update the matched rows, I can also do something about the rows that exist in table TEST02 that have now matched record in table TEST01. In this example, I have inserted the rows from table TEST02, other options allow me to either delete the row from table TEST01 or raise a SIGNAL statement.

And that NOT ATOMIC CONTINUE ON SQLEXCEPTION specifies that if an error is encountered on the insert, the MERGE will continue. Bad data happens, this saves be the burden of constantly having to refine my selection criteria to avoid the crap.

And a final note on the subject of selection criteria, the WHEN MATCHED and WHEN NOT MATCHED can both be extended with an AND to refine the selection to exactly what you want.

Do go and read up on the MERGE statement. It provides a powerful and flexible approach to updating tables from source data and one that I will be using in the very near future.

Flattr this!

Quote of the Day: The inconvenience of convenience

The bright side of convenience is an attractive one: it promises us an easier life. Convenience, like pleasure, is an important component of a good life. If we didn’t choose convenience every now and again our lives would be hopelessly uncomfortable and inefficient.

But let’s not forget that convenience can also lead to undesirable paths – it can even kill. Convenience often leads us to to have sedentary lifestyles, support businesses that harm society, have unsatisfactory daily routines, to be uneducated, and politically apathetic.

It is inconvenient to only buy from socially responsible businesses, to exercise, to find new things to do, to keep well informed, to vote and protest when governments commit injustices. A good life demands a reasonable degree of struggle – the right balance between the ease of convenience and the benefits of meaningful efforts. Like pleasure, convenience has to be weighed against the price we are paying for it, and the short- and long-term consequences that might ensue.

Carissa Véliz

Flattr this!

Corbyn on Brexit: Labour not wedded to a principle

I have to hand it to Jeremy Corbyn: he never ceases to amaze.

Back in September, he said:

It isn’t migrants that drive down wages, it’s exploitative employers and the politicians who deregulate the labour market and rip up trade union rights.

It isn’t migrants who put a strain on our NHS, it only keeps going because of the migrant nurses and doctors who come here filling the gaps left by politicians who have failed to invest in training.

It isn’t migrants that have caused a housing crisis; it’s a Tory government that has failed to build homes.

This was quite a remarkable position for Corbyn to take as it it managed to be principled, consistent and true.

So, it was probably inevitable that he would abandon it:

Labour is not wedded to freedom of movement for EU citizens as a point of principle.

But nor can we afford to lose full access to the European markets on which so many British businesses and jobs depend. Changes to the way migration rules operate from the EU will be part of the negotiations.

It’s not that he’s trying to weasel his way into an imaginary compromise between the leave and remain factions of his own party that is so impressive, it’s the spectacularly inept manner in which he has attempted to do it.

The best thing that Labour could do now is pack their collective bags and go home in order to leave the way clear for a competent and progressive alternative to emerge.

Flattr this!