I recently posted this on my personal blog, however it's quite a useful tip so I thought I'd post it here too. Sometimes you may want to perform a mass update on a table based on the contents of another table. The first thing you may think of is using a cursor to scroll through Table A and update Table B line by line. This is extremely slow. However, there is another way.

The way to do it is to join the two tables together then perform a self-update. I'll called the first table "Product" and the second table "UpdatedProduct".

UPDATE Product
    SET Price = NewPrice
    FROM Product
INNER JOIN UpdatedProduct ON UpdatedProduct.ProductId = Product.ProductId

So what have I done there? Well the first two lines instruct the dbms to update the Product table, altering the price. The second two lines provide a join of the 2 tables we are using and an interim relation that the Update can use as it's source. A table of 250,000 records took 3 seconds to update with this method but over 30 minutes using a cursor so the benefits are huge.

If you engage in any kind of e-commerce then chances are you'll soon encounter the term "3D Secure". This is a security system introduced by Visa and Mastercard to protect against internet fraud. Yawn, pretty dull right. Well it is but it has a couple of real benefits for both e-commerce customers and e-commerce website owners. The most important of which is liability shift. This means that you, yes you Mr Website Owner, are protected against most fraudulent transactions. Given that recent reports state that Internet card fraud is rising at a rapid rate, "3D Secure" seems a no-brainer for most e-commerce sites. 

Still awake?

Good, excellent, keep with me because good stuff is on the way. "3D Secure" comes in two forms: "Verified by Visa" and "Mastercard Secure Code". Great, it's like a repeat of the VHS vs BetaMax wars. Well, not quite. Lucky for us Visa and Mastercard appear to have tackled the problem together and implementing both schemes into a website can be done simulataneously. Frankly that's good news for developers like myself. What's not good news is that the process of getting "3D Secure" into an existing site can be a little jarring and may require substantial changes to your payment gateway. The essence of the changes is the receipt of a ParEq code from your internet payment provider which needs to be sent to the card holders bank but more about that in a later article.

But is this optional?

Yes and no. Visa and Mastercard are strongly pushing the scheme and it's expected to become mandatory at some point in the near future. However most internet payment providers have already made "Mastercard Secure Code" mandatory for the acceptance of the UK Maestro cards. This means that if you want to accept the Maestro card, and you really do, then you will need to implement the "3D Secure" scheme on your site to do so. Existing sites that accept Maestro but do not accept "3D Secure" will soon cease to function. Fear not as in my next post I'll explain how to modify an existing .NET based payment gateway to accept "3D Secure" payments. You'll need your techy hat on and, in true Blue Peter style, a toilet roll and sticky-backed tape. Stay tuned.