Sql tip #2 – Update table using a join

Occasionally I’ve had to update a database table with a value from another database table.

Now I’m not talking about updating a single value in a single row, that wouldn’t be hard with an update statement, we’re talking billions and trillions of rows… realistically though hundreds.

So we have two or more tables that we can join in a sql query.
Let’s say we have a tables TableA and TableB and that TableB has a foreign key referencing TableA.
A select query joining those two would look something like this:

 B.ShippingDate -- just so B won't be left out
 TableA A
  ON A.Key = B.ForeignKey

That was easy. A simple inner join and we have all the columns we need.

An update through a join follows pretty much the same rules in the join department but replaces the SELECT with an UPDATE.
For the sake of the demo let’s now say TableA and TableB have the “same” column (as seen above) and that for every row in one table (TableA) we need to update the matching rows in the other (TableB).
For the sake of the demo let’s assume this value is a shipping date when an order is sent to a customer. We’ve set the value on TableA and now we need to mark all referenced rows in TableB with the same value, taken from TableA.

This is how it would be achieved:

 SET B.ShippingDate = A.ShippingDate
 TableA AS A
  ON A.Key = B.ForeignKey

As you can see this SQL statement is using the same join as the first SQL statement, the only difference is the Update syntax on top.

So… like so many other problems this is not that tricky once you know how to do it.

And now you know 😉



Sql tips #1 – The “SELECT INTO” clause

One of the reasons why I started this blog was so I could gather my code stuff in one place. I have been using Google Docs (now Google Drive) for them but where’s the fun in that?
They are a lot easier to share using a blog, right?

So here goes one coding tip.

On some occasions I’m working on a new feature or change that needs some demo data. Some data that is as close to the current version of the live data as possible.
The difficult way of achieving that would be to script up the old table, create a new table using that script but with a different name, and then insert a lot of data into it through some, for sake of demonstration, ridiculously difficult process.

Or we could use SELECT INTO.
By using SELECT INTO we both create a table and populate it with data in one fell swoop.

-- We can select all the columns of a table into the new table:
INTO [DatabaseName].[SchemaName].[NewTableName]
FROM old_tablename;

As stated before what this does is create a new table with columns identical to the columns of the old table and populate the new table with all the data from the old table.

Neat huh?

The next one is too:

-- We can also select only the columns we want into the new table:
SELECT column_name(s)
INTO [DatabaseName].[SchemaName].[NewTableName]
FROM old_tablename

Same trick as before but now you’re selecting which columns to create in the new table along with copying the data that belongs to those columns.

I should warn you:
This script doesn’t copy over any indexes (indices) or triggers or any complimacated doowackies. Only data.
My database engine of choice is Microsoft’s SQL Server which you can get here for the nifty price of free (the express version that is).

I haven’t tested this script on any other database server and I don’t know if it’s ANSI compliant. 🙂

Fair enough?

blog rant

Been meaning to try this for a while…

This is a “Hello World” post.

Imagine that!
A computer nerd with a blog.
Been working my way towards this for many years, always meant to code my own blog engine and always gave up somewhere mid-way.

So I’m giving WordPress a try.
It’s supposed to be King, right?
I mean a blog engine that has almost 30million results about alternatives to it must be somewhat decent.

As to what I’m planning to blog about?
Computer nerdness, mostly.

Setting up this blog, for instance.
How to get the most out of WordPress plugins and such.
How to get or design a metro theme for this blog would be another topic.
This one is great but I’m to stingy to pay the 50 bucks for it, so I might just try and make my own. That way I’ll be forced to take a peek into the world of css and php which I’ve meant to do for a long time… but there is just so little time and computer games have used up most of that resource.

Let’s leave it at that for the time being, shall we