May 18, 2011 at 3:00 am
Hi all,
It's a long time since I had to do anything like this, and I really can't remember how.
I need to update many rows in a table, each with its own postcode, i.e. update property table set workarea = postcode from the same row. It looks like this:
Table - ctproperty
property_ref varchar6
workarea char8
postcode char8
So for each property ref I need to update the workarea field with the postcode for the same property ref.
I assume some sort of outer join?
Please can anyone help?
Thanks,
Paula
May 18, 2011 at 4:29 am
Hi,
This is the closest I can get to what I need, but it gives an ambiguous table error. Please can anyone give me a clue? Apologies for the old style joins, I really haven't done anything like this for a long time.
Thanks,
Paula
update ctproperty
set workarea = (select SUBSTRING(REPLACE(a.postcode, ' ', ''), 1, 4)
from ctproperty a, ctproperty b
where a.property_ref = b.property_ref)
from ctproperty a, ctproperty b
where a.property_ref = b.property_ref
May 18, 2011 at 4:33 am
Not sure as to your reasons, but here is the corrected syntax.
BEGIN TRANSACTION
UPDATE a
SET a.workarea = Substring(REPLACE(b.postcode, ' ', ''), 1, 4)
FROM ctproperty a
LEFT OUTER JOIN ctproperty b ON a.property_ref = b.property_ref
SELECT * FROM ctproperty WITH(NOLOCK)
ROLLBACK
--COMMIT
Bear in mind that the above could be written as I have done below: -
BEGIN TRANSACTION
UPDATE ctproperty
SET workarea = Substring(REPLACE(postcode, ' ', ''), 1, 4)
SELECT * FROM ctproperty WITH(NOLOCK)
ROLLBACK
--COMMIT
May 18, 2011 at 4:39 am
That's great, thanks! I overcomplicated it as usual!
And yes, I'm not sure of the reasons either!! Except that our customers have found a flaw in the application and need me to do this to correct the data. I just do as I'm told, not my application!
Thanks again.
May 18, 2011 at 4:39 am
Paula
you may have a problem with the way you are stripping the start of the postal code, especially those that only have a single letter and single digit at the begining, eg B1 3AC will give you B13A.
I do have a script that parses the Postcode properly unfortunately i dont have it on hand as im not in the office.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 18, 2011 at 5:01 am
Jason-299789 (5/18/2011)
Paulayou may have a problem with the way you are stripping the start of the postal code, especially those that only have a single letter and single digit at the begining, eg B1 3AC will give you B13A.
I do have a script that parses the Postcode properly unfortunately i dont have it on hand as im not in the office.
Something like this perhaps?
--Test data
DECLARE @ctproperty AS TABLE (property_ref INT, postcode VARCHAR(8),workarea VARCHAR(4))
INSERT INTO @ctproperty(property_ref, postcode)
SELECT 1, 'B1 3AD'
UNION ALL SELECT 2, 'ST16 4EG'
--Update statement
UPDATE a
SET a.workarea = b.workarea
FROM @ctproperty a
LEFT OUTER JOIN (SELECT
tbl.property_ref,
REVERSE(SUBSTRING(REVERSE(tbl.postcode),MAX(PATINDEX(postcode.pattern,REVERSE(tbl.postcode))),LEN(tbl.postcode))) AS workarea
FROM @ctproperty tbl
CROSS JOIN (SELECT '% [0-9][A-Z]'
UNION ALL SELECT '% [0-9][A-Z][A-Z]'
UNION ALL SELECT '% [A-Z][0-9][A-Z]'
UNION ALL SELECT '% [0-9][0-9][A-Z]'
UNION ALL SELECT '% [A-Z0-9][0-9][A-Z][A-Z]') AS postcode(pattern)
GROUP BY tbl.property_ref, tbl.postcode) b ON a.property_ref = b.property_ref
SELECT * FROM @ctproperty
May 18, 2011 at 5:15 am
Bizarrely that is exactly what is needed, I have been told.
They want the first 4 digits regardless of the format!
But thanks for caring 🙂
May 18, 2011 at 5:22 am
Its something very similar yes, skcadavre, though i think you're missing an | in the last regex expression.
No problem Paula, I've been asked to do similar things in the past and found out it wasnt what they really wanted.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 18, 2011 at 6:56 am
Just the heads up. NEVER EVER use nolock unless you're prepared to get crap data (not enough or too much of the valid data). Nolock is not a magic get it fast button. It has major consequences and I NEVER had a client approve of its use once they understood the consequences.
May 18, 2011 at 8:42 am
Ninja's_RGR'us (5/18/2011)
Just the heads up. NEVER EVER use nolock unless you're prepared to get crap data (not enough or too much of the valid data). Nolock is not a magic get it fast button. It has major consequences and I NEVER had a client approve of its use once they understood the consequences.
Umm, did you look at where I used it? It was in the middle of an open transaction in which I had first updated the table. It was more to allow the OP to see what they were updating so that they'd know that with no where clause in evidence they would be updating the entire table.
May 18, 2011 at 8:46 am
Still don't need the nolock there. The update is done and you'll be able to read from the table.
May 18, 2011 at 9:43 am
Ninja's_RGR'us (5/18/2011)
Still don't need the nolock there. The update is done and you'll be able to read from the table.
Had you written that the first time I wouldn't have had any problem. . . instead I received the all capital shouting, made my hackles rise 😉
May 18, 2011 at 9:55 am
skcadavre (5/18/2011)
Ninja's_RGR'us (5/18/2011)
Still don't need the nolock there. The update is done and you'll be able to read from the table.Had you written that the first time I wouldn't have had any problem. . . instead I received the all capital shouting, made my hackles rise 😉
That's the point. People think there's no consequence except that your code runs faster. Actually it runs bad and sometimes quicker. BIG difference.
Nothing against you, just the general conception of the idea.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply