artful at rogers.com
artful at rogers.com
Mon Aug 14 07:50:13 CDT 2006
To this I would add, why use a physical column at all? Why not define a calculated column and then index it? That would be a much more efficient way of doing what you want, I think. Arthur ----- Original Message ---- From: "Haslett, Andrew" <andrew.haslett at ilc.gov.au> To: dba-sqlserver at databaseadvisors.com Sent: Monday, August 14, 2006 5:32:19 AM Subject: Re: [dba-SQLServer] Update query leaves null Why not just build a unique index / constraint on the existing 4 fields? This will prevent duplicates from being created at all... -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Saturday, 12 August 2006 6:08 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Update query leaves null I need to build a new field on a table, and insert the first N characters of Y other fields into that new field. This will be known as a match code and it is an accepted and known method in the client's industry of weeding out duplicates. For example I need to create a new string with the first 5 characters of the first name, last name, address1, address2, zip and the plus4. I will write this new string back into a new field. What I want to do is to build a unique index on the field. Then as the update occurs, I need a silent failure if the match code already exists. Thus if the same exact match code is already in the table, just leave a null of empty string in that match field for the duplicate record. Doing this would allow me to then go back and delete the records where the match code is null and "de-dupe" the table. If I do an update query in SQL, will it do this silent failure or will it stop and announce the failure? Do I need to do something specific to get a "silent failure"? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com IMPORTANT - PLEASE READ *** This email and any files transmitted with it are confidential and may contain information protected by law from disclosure. If you have received this message in error, please notify the sender immediately and delete this email from your system. No warranty is given that this email or files, if attached to this email, are free from computer viruses or other defects. They are provided on the basis the user assumes all responsibility for loss, damage or consequence resulting directly or indirectly from their use, whether caused by the negligence of the sender or not. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com