TOPIC: INSERT
Dealing with Error 1064 in MySQL queries
27th April 2023Recently, I was querying a MySQL database table and got a response like the following:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
The cause was that one of the data column columns was named using the MySQL reserved word key. While best practice is not to use reserved words like this at all, this was not something that I could alter. Therefore, I enclosed the offending keyword in backticks (`) to make the query work.
There is more information in the MySQL documentation on Schema Object Names and on Keywords and Reserved Words for dealing with or avoiding this kind of situation. While I realise that things change over time and that every implementation of SQL is a little different, it does look as if not using established keywords should be a minimum expectation when any database tables get created.
Updating Oracle data tables that have associated sequence objects
3rd May 2007Here’s something that I want to put somewhere for future reference before I forget it: keep sequences associated with Oracle data tables up to date while adding records. Given that it took me a while to find it, it might come in useful for someone else too.
The first thing is to update the sequence itself:
SELECT TABLE_SEQ.NEXTVAL FROM DUAL;
Dual is a handy single record table that you can use to update sequences. Use the actual associated table itself if you like to see that sequence number rocket…
The next thing is to use the new value to assign a table ID as part of an INSERT statement:
INSERT INTO “TABLE” VALUES (TABLE_SEQ.CURRVAL, 1, ‘Test value’);