Thread: Update function

Hi all,

I have a table with products in and each products has 13 a digit barcode
as its primary key.  There
are two basic types of barcodes - those that start with 25 and those that
start with 60.  For barcodes that start with 25 only the first six digits
matter, the rest are just check digits.  Unfortunately, the full barcode
has been parsed into the database, resulting in duplicate product
information. eg. 2543210000222 and 2543210000123 are the same product.
However, the real problem is that the barcode 2543210000000 may also
exist.

I need some way to get rid of all these duplicates and I also need a way
to change all the 25 barcodes such that the barcode consists of the first
6 digits and 7 zeros e.g. 2543210000123 becomes 2543210000000 (I need the
barcode to be 13 digits)

I'm really stuck.  Any suggestions would be welcome.  What would really be
useful is an example on how to move through a result set one record at a
time, check if that changing the barcode on that current record does not
result in an alreadly existing barcode.  If it doesn't, then then record
is updated through an update, if it does, the record is deleted.

HELP

Thanks


--
Rob

He who dies with the most toys ...

                    ... still dies

On Friday 12 April 2002 11:42 am, you wrote:
> Hi all,
>
> I have a table with products in and each products has 13 a digit barcode
> as its primary key.  There


I'm not sure what you mean, "has 13 a digit barcode...".   Is that a style of
barcoding?

> I'm really stuck.  Any suggestions would be welcome.  What would really be
> useful is an example on how to move through a result set one record at a
> time, check if that changing the barcode on that current record does not
> result in an alreadly existing barcode.  If it doesn't, then then record
> is updated through an update, if it does, the record is deleted.
>
> HELP
>
> Thanks


I would suggest that you put barcode data in a separate table "barcodes" with
primary key the same as your "products" primary key (foreign key in barcodes
table) and change your products table to use a serial data type for primary
key (rather than the current "barcodes" field).  Doing those two things
should make it possible to query for barcodes that point to the same product;
therefore, duplicate barcodes for the same product wouldn't matter.  As I
understand it, barcodes may be assigned to the same product for miriad
reasons; e.g., special pricing, etc.

Also, if you don't want duplicate barcodes ("barcode" table), set a
constraint for the "barcode" column to UNIQUE.

HTH
Raymond

Had to do something quite similiar recently, so here goes...

-- find one of each 6-digit start combinations; note that sorting makes
-- sure that 123456,12345600000000 comes before 123456,12345600000001 and is
-- therefore selected by 'distinct' with preference
-- the strange hack with 'order by' is required to make 'distinct' happy
SELECT DISTINCT ON (substr(code, 1, 6)) code INTO tmpcodes FROM products
    WHERE code LIKE '25%' ORDER BY substr(code, 1, 6), code;

-- index temp table, may be large
CREATE INDEX tmpcodes_idx ON tmpcodes(code);

-- delete duplicates
DELETE FROM products WHERE code LIKE '25%' AND NOT EXISTS
    (SELECT 1 FROM tmpcodes WHERE tmpcodes.code=products.code);

-- unify remaining codes
UPDATE products SET code=substr(code, 1, 6) || '0000000'
    WHERE code LIKE '25%';

-- drop temp table
DROP TABLE tmpcodes;

In the future you want to make sure that this does not happen again, so
create a unique index to prevent inserting 252345600000001 when 252345600000000
exists:

-- function to extract first 6 chars
CREATE FUNCTION barcode6(text) RETURNS text AS 'SELECT substr($1,1,6);'
    LANGUAGE 'sql' WITH (iscachable,isstrict);

-- partial index on first 6 chars; requires Postgres >=7.2 I think
CREATE UNIQUE INDEX barcode6_idx ON products (barcode6(code))
    WHERE code LIKE '25%';

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
The past: Smart users in front of dumb terminals       /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |