0

I am getting a vlue from user and entering it inside my table in database. But I have to throw the user an error if he enters a value inside a data field which is already taken.

UPDATE Class_records SET The_number= '456' where class='mech-4B'

So here am updating my table by setting The_number to '456' to every records under mech-4B .

What kind of query statement can I do so that when another user enters the same number 456 say to "cse-4B" the query condition should fail ?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Obito Uchiha
  • 113
  • 1
  • 8
  • 1
    So multiple records can have the same value, but only if they're set in a single operation? – Robby Cornelissen May 20 '19 at 07:00
  • Which dbms are you using? I'd probably go with a trigger based solution here. – jarlh May 20 '19 at 07:03
  • What is your RDBMS? Use a procedure to update your table, and check for existing of data before update. – Pham X. Bach May 20 '19 at 07:03
  • @RobbyCornelissen yes multiple records can have the same value(The_number) **only** when they have the same class name (mech 4b cse 4b etc). if someone assigns The_number to mech 4B that number can't be usable by any other now – Obito Uchiha May 20 '19 at 07:04
  • @jarlh Microsoft SQL – Obito Uchiha May 20 '19 at 07:04
  • 1
    Possible duplicate of [Add unique constraint to combination of two columns](https://stackoverflow.com/questions/15800250/add-unique-constraint-to-combination-of-two-columns) – Matt May 20 '19 at 07:10
  • `CREATE PROCEDURE UpdateAWBUnique @AWB as nvarchar, @Internal as nvarchar AS BEGIN SET NOCOUNT ON if((SELECT count(*) From Def_tab WHERE AWB=@AWB) == 0) BEGIN UPDATE Def_tab SET AWB= @AWB where Internal=@Internal END ELSE BEGIN PRINT 'Already Exits' END END GO` I have written this procedure but somehow it keeps returning with errors namely with ERROR 102 SQLSTATE 420000 – Obito Uchiha May 20 '19 at 09:12

3 Answers3

0

You can run the query and check the number of row affected by the query. If the same query is runned, the number of row returned is most probably is zero. If zero you can throw error or expection to user.

if (rowCount() > 0){
    //value is new 
}
else {
    //value is duplicated and throw error.
}
Arsat
  • 26
  • 2
  • Wouldn't the sql query throw an exaception, instead of returning "0 affected rows"? – Marvin Klar May 20 '19 at 07:18
  • if you use pdo, we can call method rowCount() which return number of affected row, if not affected thus will return 0 – Arsat May 26 '19 at 13:39
0

So you have to write a stored procedure for it:

CREATE PROCEDURE UpdateXYZTableWithUniqueness
    (@The_number AS INT,
     @class AS NVARCHAR)
AS 
BEGIN 
     SET NOCOUNT ON 

     IF ((SELECT COUNT(*)  
          FROM Class_records 
          WHERE The_number = @The_number) == 0)
     BEGIN
         UPDATE Class_records 
         SET The_number = @The_number 
         WHERE class = @class 
     END
     ELSE
     BEGIN
         PRINT 'Already Exits'
     END
END
GO
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Sakare
  • 180
  • 7
  • how does this code change if The_number is an NVARCHAR type – Obito Uchiha May 20 '19 at 09:02
  • `CREATE PROCEDURE UpdateAWBUnique @AWB_No as nvarchar, @InternalPOD as nvarchar AS BEGIN SET NOCOUNT ON if((SELECT count(*) From Def_tab WHERE AWB_No=@AWB_No) == 0) BEGIN UPDATE Def_tab SET AWB_No= @AWB_No where InternalPOD=@InternalPOD END ELSE BEGIN PRINT 'Already Exits' END END GO` somehow it keeps returning with errors namely with ERROR 102 SQLSTATE 420000 – Obito Uchiha May 20 '19 at 09:13
  • **BE CAREFUL!!** [Bad habits to kick : declaring VARCHAR without (length)](http://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx) - you should **always** provide a length for any `varchar` variables and parameters that you use. Your `@class` here is a parameter of **exactly ONE character** in length !! Is that really what you wanted????? – marc_s May 23 '19 at 20:32
0

If you work with mysql, you can try to add "ignore" after your update : "Update Ignore Class_records set ... ".

You can look this article : https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/

If you work with T-SQL, you can try to use the "If not exists ..." : "If not Exists (select * from Class_Records where The_number ='456' and class ='mech-4B') Begin UPDATE Class_records SET The_number= '456' where class='mech-4B' end"

you can look this article : SQL Server 2008 - IF NOT EXISTS INSERT ELSE UPDATE

bye

nalypto
  • 1