Re: Update function

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