Another SQL question

[PaulStat]

Managed to solve my other issue so thanks for that. Anyway I have come across the following SQL, which I don't quite understand

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

The first select seems to be a substitute for the VALUES(x, x, x,x) so I understand that. I don't understand the where not exists select however, what in blue blazes does select 1 do in this case?

 

[PaulStat]

I was essentially wanting to do INSERT INTO X (column) VALUES(value) WHERE NOT EXISTS (SELECT * FROM X WHERE column = value). But discovered I couldn't combine VALUES and WHERE. A Google came up with the above, which I tried to adapt but it seemingly didn't work.

I have now instead created a unique column and used "INSERT IGNORE INTO X (column) VALUES(value)" which works just fine

 

[PaulStat]

Actually no it didn't I have duplicates! GAH!

 

[PaulStat]

It's a two column table I have.

CREATE TABLE IF NOT EXISTS PERFORMER (
                                      PERFORMERID INT NOT NULL AUTO_INCREMENT, PERFORMER VARCHAR(70), PRIMARY KEY(PERFORMERID), UNIQUE(PERFORMER), INDEX(PERFORMER)
                                  ) ENGINE=INNODB

I had hoped the following insert would only insert unique fields and ignore any errors raised by duplicates. But as I mentioned I do get duplicates

INSERT IGNORE INTO PERFORMER (PERFORMER)
                         VALUES (%s)

 

[PaulStat]

I'm using Python and MySQLdb

          sqlString = """INSERT IGNORE INTO PERFORMER (PERFORMER)
                         VALUES (%s)"""
          data = [(c) for c in self._performers]
          self._cursor.executemany(sqlString, data)

 

[PaulStat]

Wouldn't it be incredibly slow to insert that way?

 

[PaulStat]

Well in the end I did it the programmatic way i.e. select based on the performer if no results insert that performer.

19k records when the table was empty took 18 seconds, and running the same data/logic again to check it didn't insert duplicates took 10 seconds. So not too bad I suppose.

 

[PaulStat]

:edit nevermind, being dim