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!

New Year, New Look

With the latest release of WordPress, the Twenty Seventeen theme was also released. And I’ve not only managed to find a bit of time to play around with it, but I’ve also cleaned up some of the sidebar links while I was at it. I’m not entirely sure about the massive header image, but the theme itself seems to work reasonably slickly so I shall see how it goes.

And, as we venture cautiously into 2017, all that remains is for me to with you all the best of luck for the new year.

Congratulations on surviving 2016, here’s hoping we all make it through the next 365 days.

Happy New Year Danbo.
Photo by Leland Francisco. Click image for original.

Flattr this!

OSPAT: The Open Source POWER Availability Tool

Here’s handy. If you are using Linux on the IBM Power architecture, the Open Source POWER Availability Tool (OSPAT) allows you to search multiple distributions for available packages.

[T]he specialty of OSPAT is that it only returns POWER package results, it does not return x86 packages in the search results. Thus, the user doesn’t have to drill deep into the search results to find the POWER packages. Also, the search results display the results across multiple distributions, helping POWER users determine whether certain packages have equivalency across multiple distributions.

Click here if you want to take a look.

Flattr this!

Portable Vim in Powershell

Because I have finally been able to replace my work laptop.

Because I’m allowed to install software on a server, but not on the laptop in front of me.

Because corporate security policies are insane.

Because I need a decent text editor.

Because PortableApps is a lifesaver.

I have talked about getting Vim to work in Powershell in the past, but this time around I need to get the portable version of Vim working in Powershell.

The first step, of course, is to download the PortableApps platform and install gVim. Handily, this brings the Vim binary along with it so you just need to point your Vim alias to this.

This is done by editing your $profile so that the following line is included:

set-alias vim "Path\To\PortableApps\gVimPortable\App\vim\vim80\vim.exe"

Everything else appears to be working as expected, so I can actually get some work done now.

Flattr this!

Eight questions to ask before buying that IoT gadget

Because, Of course smart homes are targets for hackers

  • Does the vendor publish a security contact? (If not, they don’t care about security)
  • Does the vendor provide frequent software updates, even for devices that are several years old? (If not, they don’t care about security)
  • Has the vendor ever denied a security issue that turned out to be real? (If so, they care more about PR than security)
  • Is the vendor able to provide the source code to any open source components they use? (If not, they don’t know which software is in their own product and so don’t care about security, and also they’re probably infringing my copyright)
  • Do they mark updates as fixing security bugs? (If not, they care more about hiding security issues than fixing them)
  • Has the vendor ever threatened to prosecute a security researcher? (If so, again, they care more about PR than security)
  • Does the vendor provide a public minimum support period for the device? (If not, they don’t care about security or their users)

Flattr this!

Switching from LightDM to GDM

Yesterday, when I updated my Antergos box, it pulled down lots of shiny new Gnome stuff. Today, when I booted up my laptop, I discovered that the greeter wasn’t working any more and I couldn’t sign in or get at any of that shiny new Gnome stuff.

I have encountered this issue before and it was caused by the Antergos LightDM greeter theme. The easiest solution, therefore, is to switch to GDM so that I am using all Gnome all of the time.

It’s a painless enough process, but I am recording the steps here so I can easily look up the steps when I need them again.

So…

CTRL-ALT-F2 to get into TTY2

Log in and switch to root. Then…

systemctl stop lightdm
systemctl disable lightdm
pacman -S gdm
systemctl enable gdm
systemctl start gdm

And you’re done.

Update

And I notice today (Friday 14th) that I now have an updated lightdm-webkit2-greeter which probably solves yesterday’s issue. That said, I will stick with GDM for now just to see how things go.

Flattr this!

Great UI design from LinkedIn

Although I have a LinkedIn account, I don’t often look at it. But today was one of those rare moments that I not only looked at the site but I even tried to leave a comment. And here’s what LinkedIn said:

There was a problem sharing your update. Please try again.

After a bit of experimenting, it appears that LinkedIn has an undocumented character limit. My original 774 characters was problematic, but once I’d cut it doen to 670 characters the problem went away. So I’m guessing there’s a 700 character limit on LinkedIn comments.

But seriously, if this is the problem, why can’t the site damn well say so. “There was a problem sharing your update,” means nothing and telling people to just try again is a guaranteed method of causing frustration and losing attention. Is it really so difficult to say “Please shorten your comment to 700 characters”.

Or, better still, provide a little decrementing counter of the sort you see on the Quitter UI for GNU social.

Flattr this!

Remounting a read-only filesystem on Android

Following on from my last post, I figured that copying the hosts file from my laptop to my phone would be a very good idea. In principle, this is just a case of getting the file onto my phone and then copying it to /etc/hosts.

Obviously, I need root access to do this but, with a Fairphone 1 this is not a problem.

What did catch me out, though, is that /system is mounted as a read only file system. It’s not difficult to get around, but I am noting it here so I can easily look up the steps when I next do this.

# mount -o rw,remount /system
# cp /storage/sdcard0/Download/hosts /etc
# mount -o ro,remount /system

For other phones, some pathnames may vary.

Flattr this!