mysql - SQL插入到不存在的值中

Question

我正在尝试执行以下查询

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')

它给出错误。我哪里错了？

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

还有其他解决办法吗？我有主键作为自动编号..

Answer 1

参见 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'
)