NimbleText

Manipulate text and data with light-weight patterns

How to Generate Update Statements

Assuming you have some data already, perhaps as raw text, generating a SQL update statement should be trivial, but it can end up being a lot of grunt work.

This is exactly the kind of situation where NimbleText shines. It feels great ridding yourself of this kind of grunt work, just watch!

And if you get lost for a moment don't worry. Just drink it in, let it wash over you. It'll click soon enough.

Let's say we've got this data:

ID, First Name, Last Name, Company
1, Walter, Greer, Aratos Pty Ltd
2, Conrad, Dunbar, Lupus Tech
3, Mandy, Miron, Horse Lane Pottery

We want to generate a query, like this, but for every row:

Update Contacts
Set FirstName = 'Walter', LastName = 'Greer', Company = 'Aratos Pty Ltd' 
where ID = 1

In NimbleText, we paste our data into the first textbox (the data textbox). Then we create a pattern like this:

try it → 
Update Contacts
Set FirstName = '$1', LastName = '$2', Company = '$3' 
where ID = $0

This will produce the following output:

Update Contacts
Set FirstName = 'First Name', LastName = 'Last Name', Company = 'Company' 
where ID = ID
Update Contacts
Set FirstName = 'Walter', LastName = 'Greer', Company = 'Aratos Pty Ltd' 
where ID = 1
Update Contacts
Set FirstName = 'Conrad', LastName = 'Dunbar', Company = 'Lupus Tech' 
where ID = 2
Update Contacts
Set FirstName = 'Mandy', LastName = 'Miron', Company = 'Horse Lane Pottery' 
where ID = 3

NimbleText is free, by the way. You can use it online, or even better, you can download it and use it even when you're offline.

You can copy this out, being careful to avoid the first row (it contains the headers) and now you have your update statements. Job done.

Skip the header row

There's only one refinement left to the technique above: we want to skip the header row. For this we'll use the $each+ ('each plus') keyword.

Here's our refined pattern:

try it → 
$each+
Update Contacts
Set FirstName = '$1', LastName = '$2', Company = '$3' 
where ID = $0

The '$each' token means 'Repeat the following bit for each row of our raw data. More specifically, the '$each+' (each PLUS) token means "skip the header row, and then repeat this pattern for each data row.

So given that data and that pattern we get the following result.

Update Contacts
Set FirstName = 'Walter', LastName = 'Greer', Company = 'Aratos Pty Ltd' 
where ID = 1
Update Contacts
Set FirstName = 'Conrad', LastName = 'Dunbar', Company = 'Lupus Tech' 
where ID = 2
Update Contacts
Set FirstName = 'Mandy', LastName = 'Miron', Company = 'Horse Lane Pottery' 
where ID = 3

And there we have it, a custom update generator, and all the wisdom you need to tailor your own custom update generators.

Of course this is just one type of SQL query that NimbleText will help you generate.

We've also written guides on many other ad-hoc queries you can generate:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText