Update statement

Results 1 to 2 of 2

Thread: Update statement

  1. #1
    Join Date
    Dec 1969

    Default Update statement

    Hi all; i&#039;m having a bit of trouble here, wondering if any of you can help....<BR><BR>i have two tables:<BR><BR>tblTemp (approx 1500 rows):<BR><BR>[RegionCode] char (2) NULL,<BR>[Region] char (30) NULL, <BR>[CountyId] char (4) NULL,<BR>[County] char (30) NULL, <BR>[TownId] char (5) NULL,<BR>[Town] char (30) NULL,<BR>[PostCode] char (7) NULL,<BR>[Easting] char (6) NULL,<BR>[Northing] char (6) NULL<BR><BR>sample:<BR>02 SOUTH EAST 066 OXFORDSHIRE 10946 WALLINGFORD OX10 NULL NULL<BR>02 SOUTH EAST 047 KENT 10951 EDENBRIDGE TN8 NULL NULL<BR>02 SOUTH EAST 083 SURREY 10968 COULSDON CR5 NULL NULL<BR>02 SOUTH EAST 090 WEST SUSSEX 10969 WORTHING BN12 NULL NULL<BR>02 SOUTH EAST 042 HERTFORDSHIRE 10978 HERTFORD SG13 NULL NULL<BR><BR>and<BR><BR>tblLkpPostcodeGrid (approx 1.7 million rows)<BR><BR>pCode char(7)<BR>easting char(6)<BR>northing char(6)<BR><BR>sample:<BR>AB107FS 03912 08033<BR>AB107FT 03912 08033 <BR>AB107FW 03920 08045 <BR>AB107FX 03919 08045 <BR>AB107FZ 03930 08047 <BR><BR><BR>Anyway, i&#039;m trying to do an update to tblTemp to &#039;attatch&#039; a &#039;full postcode&#039;, instead of the first half, using the query;<BR><BR>update<BR> tblTemp<BR>set<BR> postcode = <BR> (<BR> select top 1 pCode<BR> from tblLkpPostcodeGrid b<BR> where<BR> b.pCode like replace(tblTemp.PostCode, &#039; &#039;, &#039;&#039;) + &#039;%&#039;<BR> )<BR><BR>but it is just so slow (i just left it running for an hour and fifteen mins and it still hadn&#039;t finished; this is unacceptably slow.<BR><BR>Can anyone think ogf any way to speed this up, or a faster query that would return the same result. I have no indexes on either of the tables.<BR><BR>cheers<BR>cp

  2. #2
    Join Date
    Dec 1969

    Default RE: Update statement

    I&#039;ve made a change to the way the tables are linked. Previously LIKE &#039;OX1%&#039; would have matched &#039;OX1&#039; as well as &#039;OX10&#039; - &#039;OX19&#039;. I&#039;ve assumed that the postcodes are all british, with the postcodes in tblLkpPostcodeGrid being 3 characters longer than those in tblTemp.<BR><BR>update T <BR>set T.postcode = L.pCode <BR>from tblTemp T<BR>join tblLkpPostcodeGrid L<BR>on rtrim(left(pCode, len(pCode) - 3)) = T.postcode<BR><BR>I&#039;ve tried it with 2800 rows in tblTemp and 23000 rows in tblLkpPostcodeGrid and it took 2 seconds.<BR><BR>Hope this does what you want, or at least helps in some way.<BR><BR>Gavin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts