implement features: "insert ignore" AND "insert update on duplicate key"

[silverhawk184]

Prereq:

alasql("CREATE TABLE Amazon (Date STRING PRIMARY KEY,Ct INT)"); alasql("INSERT ('12/12/14',0)");

update data: (not working)

alasql("INSERT INTO Amazon VALUES ('12/12/14',0) ON DUPLICATE KEY SET Ct=5");

try to update again: (not working)

alasql("INSERT IGNORE INTO Amazon VALUES ('12/12/14',0)"); alasql("UPDATE Amazon SET Ct=5 WHERE Date='12/12/14'");

try to update again: (works but gives warning about key)

alasql("INSERT INTO Amazon VALUES ('12/12/14',0)"); alasql("UPDATE Amazon SET Ct=5 WHERE Date='12/12/14'");

[agershun]

Couple comments:

1. Put Date into square brackets, because Date is a keyword for AlaSQL:

    alasql("CREATE TABLE Amazon ([Date] STRING PRIMARY KEY,Ct INT)");

1. I want to introduce special DATE values to convert strings to proper date format, like:

    D'6/31/14/'  ==> ''20150631' or new Date('06/31/14' depending on the flag

It will allow AlaSQL to compare and convert date data without any headache.

What do you think?

1. For other operators - I will check.

[silverhawk184]

Nice catch. What if we use backtick "`" to be more semantically correct toward MySQL. I believe this feature is already implemented.

[agershun]

Yes. You can use or [column] or column for columns with spaces, special symbols, etc. as well.

[agershun]

BTW. AlaSQL distinguishes DATE and Date data types. First one is a string, while second one is JavaScript Date object type.

[mathiasrw]

AlaSQL distinguishes DATE and Date data types. First one is a string, while second one is JavaScript Date object type.

Where would be a good spot to put this in the documentation?

[agershun]

Yes. The Wiki is the most priority....

[mathiasrw]

AlaSQL distinguishes DATE and Date data types. First one is a string, while second one is JavaScript Date object type.

Could you make an example of the different use of this?

[mathiasrw]

AlaSQL distinguishes DATE and Date data types. First one is a string, while second one is JavaScript Date object type. @agershun Could you make an example of the different use of this?

[joerch80]

INSERT IGNORE INTO syntax as described by silverhawk184 is not working for me. Throwing:

INSERT IGNORE INTO prefs (Id, Desc
--------------^
Expecting 'LITERAL', 'BRALITERAL', 'EOF', 'EndTransaction', 'WITH', 'COMMA', 'AS', 'LPAR', 'RPAR', 'SEARCH', 'FOR', 'DOT', 'EQ', 'WHERE', 'SET', 'TO', 'VALUE', 'NOT', 'IF', 'FROM', 'UNIQUE', 'SELECT', 'ON', 'JAVASCRIPT', 'REPLACE', 'NULL', 'END', 'ELSE', 'UPDATE', 'DELETE', 'INSERT', 'DEFAULT', 'CREATE', 'IDENTITY', 'CHECK', 'PRIMARY', 'FOREIGN', 'REFERENCES', 'DROP', 'ALTER', 'RENAME', 'ADD', 'MODIFY', 'ATTACH', 'DETACH', 'USE', 'SHOW', 'HELP', 'SOURCE', 'ASSERT', 'COMMIT', 'ROLLBACK', 'BEGIN', 'WHILE', 'CONTINUE', 'BREAK', 'PRINT', 'REQUIRE', 'ECHO', 'DECLARE', 'TRUNCATE', 'MERGE', 'COLONDASH', 'QUESTIONDASH', 'CALL', 'BEFORE', 'AFTER', 'INSTEAD', 'REINDEX', 'SEMICOLON', 'GO', got 'INTO'

Is there a workaround for INSERT ... ON DUPLICATE KEY UPDATE?

[mathiasrw]

INSERT IGNORE is not supported.

I have a feeling you could do a select and then filter out before doing inserts - but its not pretty...