January 3, 2006 at 4:48 pm
hello everyone.
I'm pretty new to programming and was wondering if I might be able to get some wise advise.
I have a column, that the BU wants modified. The data within the column contains multiple numeric records such as 2705 00630000, 2705 4300073T, and 2705A00010000. This table is also apart of the primary key. So, what is needed is that I remove the last four zeros in a record and only the last four zeros based on a client id number. I thought of using the substring command, but this will violate the primary key constarint. Any advise I can get would be greatly appreciated.
Here is an example of what I was thinking of
UPDATE table
SET column =
REPLACE (column, substring(column, 1, 13),substring (column, 1, 4)+ substring (column, 5, 5))
from table
where client_id = 42029
January 4, 2006 at 2:13 am
If the 'column' may or may not contain spaces then this should work
SET column = LEFT(REPLACE(column,' ',''),9)
When you state this would 'violate the primary key constraint' then any modification of this column without considering the constraint will be a problem.
2 possible solutions
1.
Remove the constraint, modify the table and the primary table and then add the constraint.
2.
Duplicate the primary records except modify the matching 'column'
Modify the 'column' in the child table
Delete the unwanted primary records
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply