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:
- How to Generate Insert Statements
- How to Generate Update Statements
- How to Generate SQL 'IN' Statements
- How to List the Columns of a Table, Quickly!
- Extracting a list of columns from a block of text
- How to Generate Views the easy way
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.
- General help
- Symbols and Keywords
- Built-in Functions
- The 'Where' clause
- Date Time formatting
- Date Time parsing
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.Download NimbleText