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?

 

[Gornall]

It's essentially returning 1 (or true) where trans_id = 1 and ledger_code_1 = '2001' and says not to reinsert the record.

What's the context? have you inherited someone's code/responsibility here?

To me, it just looks like he was sticking in a test record with some test logic (e.g. where it doesn't already exist).

 

[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!

 

[skyripper]

EDIT: ignore

 

[skyripper]

Sorry worded that wrong.
Whats the table name, what are the fieldnames, what are the fixed values you want to insert, and which are the key fields?

 

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

 

[skyripper]

So the table name is PERFORMER.
The two fields are PERFORMER_ID and PERFORMER.
Both are Key fields.

So where are your Fixed Values coming from? Are you binding them into the SQL and executing the line of SQL each time you want to do an insert?

 

[skyripper]

If you are binding in values into the SQL (that's the :1 and :2 bits)

INSERT INTO PERFORMER
SELECT :1 PERFORMER_ID, :2 PERFORMER FROM DUAL
WHERE (PERFORMER_ID, PERFORMER) NOT IN (SELECT PERFORMER_ID, PERFORMER FROM PERFORMER);
​

This should work on Oracle.
DUAL is a (virtual) one row table in the database. Your SQL platform should have something similar.

 

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

 

[skyripper]

If you can't craft it in SQL from above maybe you can do a SQL lookup, then in Python decide if a row needs to be inserted or not?

I'm not familiar with coding in your two platforms. Must be some other web developers round here mind you

 

[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