CODEWITHSUNDEEP
sqlms-access
Al Ammar
Al Ammar

Reputation: 25

how to add unique values to DB table using SQL, so that there will be now duplicate rows

This is the query I am trying:

INSERT INTO Product (ProductName, Description, Brand, Size, Variety, Manufacturer, Family, Category, SubCategory ) 
VALUES ('m', 'm', 'm', 'm', 'm', 'm', 'm', 'm', 'm') 
WHERE NOT EXISTS (SELECT * FROM Product WHERE Product.ProductName = 'm')`

and this the exception I am getting:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Query input must contain at least one table or query.

Upvotes: 0

Views: 1082

Answers (3)

Andrei LED
Andrei LED

Reputation: 2699

Add a constraint for the field ProductName in the table. This way the DBMS will not allow you to insert an entry with an existing ProductName.

Also it's recommended to have a special Primary Key field (ProductId for example) populated using a sequence. It's better when you need a reference to the product from either other table or some place beyond the DB.

P.S.: using a query that check if a row with specified ProductName doesn't exist and then inserting a new row doesn't actually saves you from duplicate products in the table. Two such queries being executed simultaneously in separate transactions both will be able to successfully insert a row into the table. As a result you'll have a duplicate.

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Use INSERT ... SELECT syntax, not INSERT ... VALUES:

INSERT INTO Product 
( ProductName, Description, Brand, Size, Variety, Manufacturer, 
  Family, Category, SubCategory 
) 
SELECT
    'm','m','m','m','m','m','m','m','m'
FROM dual 
WHERE not exists 
      (select * from Product where Product.ProductName='m')

If it is for MS-Access, then you don't have a dual table available. You can either create a dummy dual table - with just one row - and use it afterwards or use this:

INSERT INTO Product 
( ProductName, Description, Brand, Size, Variety, Manufacturer, 
  Family, Category, SubCategory 
) 
SELECT
    'm','m','m','m','m','m','m','m','m'
FROM 
    (SELECT TOP 1 * FROM a_table_with_at_least_one row) AS dummy
WHERE not exists 
      (select * from Product where Product.ProductName='m')

If, however, you want to never have 2 rows with same Product.ProductName, then you could just declare the Product.ProductName column as UNIQUE and use the INSERT ... VALUES syntax - without the WHERE NOT EXISTS part, just fine:

INSERT INTO Product 
( ProductName, Description, Brand, Size, Variety, Manufacturer, 
  Family, Category, SubCategory 
) 
VALUES
    ('m','m','m','m','m','m','m','m','m')

An error will be thrown whenever you try to add a second row with the same product name as an existing one.

Upvotes: 2

N West
N West

Reputation: 6819

Your query doesn't appear to be well formed. INSERT INTO table (columns) VALUES (values) typically doesn't allow a where clause.

The documentation for MS Access SQL is here (since it appears you are using Access ODBC?) I don't see anything to indicate you can use this logic as you're trying to do.

http://office.microsoft.com/en-us/access-help/insert-into-statement-HP001032245.aspx

What you will need to do instead is query the table with your java application first to determine if a record exists with a product name of 'm', and if it does, don't fire the "insert". You could also add a constraint to the column if your dbms allows it.

Upvotes: 0

Related Questions