Viewing the log detail unfortunately file did not shed much more light on this.
The only thing I had to go on was the SQL error itself. This indicated that somewhere the system was trying to execute a nested SQL statement that looked something like the following:
select field1 from table1 where field2 = (select field3 from table2)
And that the results from table2 returned more than a single row which is not allowed as the nested relationship is using the "=" rather than "in" operator.
Armed with this information, I decided to run a SQL trace while recreating the import error. And then performed a bit of a needle-in-haystack search for something that might fit the above - using correlating times from the SQL Trace and CRM trace file (although in retrospect I think I could have searched the SQL trace for a statement like 'update organization set set state=...' as this is what happens directly after the failed error).
Lo and behold, I came across the query below which matched the kind of query pattern I was looking for. The suspect had been identified...
Running the essence of the above query in SQL it came back with the following - looks like a smoking gun to me!
In the interest of moving forward as quickly as possible, I decided to run a query that eliminate this particular error by ensuring a singleton output.
Re-running the import again subsequently succeeded... and yet another successful CRM 2013 upgrade under the belt.