CODEWITHSUNDEEP
mysqlsql
Nitin Kabra
Nitin Kabra

Reputation: 3226

SQL insert into values where not exists

I am trying to execute the following query 

INSERT INTO test (trans_id, voucher_id, trans_date, ledger_code_1,
                  company_code,trans_type, trans_amount) 
VALUES (1, 1,'2012/04/01','2001', '8523a95b-5425-46be-89ed-ebd592846845',
        'Payment', -30)   
WHERE NOT EXISTS(SELECT trans_id, ledger_code_1 
                 FROM test 
                 WHERE trans_id = 1 AND ledger_code_1 = '2001')

its giving error. where am i wrong ?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near where not exists(select trans_id,ledger_code_1 from test where **trans_id = 1 and' at line 1

is there any other solution ? i have primary key as auto number..

Upvotes: 1

Views: 10953

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50970

You might consider a different approach.

It appears from your code that you're trying to avoid the existence of two statements with the same trans_id and ledger_code_1 values. If that's a correct statement of your problem, you should protect those two columns with a UNIQUE index (or consider making them the primary key of the table if that's appropriate).

If you do that, you can issue the INSERT statement without having to worry about the logic to avoid the duplicate insertion. You will have to trap an exception if the record already exists.

The advantage of this approach is that you're guaranteed never to violate the data integrity of those two columns, even if you write additional SQL in the future and forget to include the condition, or if you modify the data in the tables directly.

The disadvantage (aside from having to trap the exception) is that it only works if you 100%, never, ever, ever want to allow those duplicate records. If there is an occasional instance in which you do (for instance, it's true of trans_id 1, but not trans_id 999) then you're stuck with applying your integrity checks in the application code.

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

See INSERT ... SELECT Syntax:

INSERT INTO test         
(trans_id,voucher_id,trans_date,ledger_code_1,company_code,trans_type, Trans_amount)
SELECT 1, 1,'2012/04/01','2001', '8523a95b-5425-46be-89ed-ebd592846845', 'Payment', -30 
from test
where not exists(
    select 1
    from test 
    where trans_id = 1             
        and ledger_code_1 = '2001'
)

Upvotes: 5

SRIRAM
SRIRAM

Reputation: 1888

  INSERT INTO test         
  (trans_id,voucher_id,trans_date,ledger_code_1,company_code,trans_type, Trans_amount)
   SELECT 1, 1,'2012/04/01','2001', '8523a95b-5425-46be-89ed-ebd592846845',
  'Payment', -30 
   where not exists(
   select trans_id=1,ledger_code_1='2001'     
      )

Upvotes: 0

Related Questions