2

I've got an SQL code to update values in a column. I need to find one query to work for MS SQL and MySQL. The main problem is that the columns which I'm using are Default and Type which are saved names in the SQL and therefore it doesn't work with the normal update statements.

I've found the following solutions, but I would like to make one query for both -

--Clearing Data logs Defualt MS SQL
UPDATE queries
SET [Default] = 0
FROM queries
WHERE [Type] = 4

--Clearing Data logs Defualt MySQL
UPDATE queries q
SET q.Default = 0
WHERE q.TYPE = 4

Thanks a lot for the help!

Morrtz
  • 47
  • 11
  • Note that for queries of any real use, this is doomed to failure pretty quick - anything that needs to do date/time math or formatting is right out. For something on about this level you may be able to abstract it with an ORM, depending on your application-layer language. Once you get into complicated queries, SQL Server (especially more recent versions) simply has better features for queries... what is the larger problem you're trying to solve? – Clockwork-Muse May 04 '14 at 14:03
  • Larget problem is application side therefore not relavent. – Morrtz May 05 '14 at 16:04

1 Answers1

3

You've got to enable ANSI quotes for both servers. If you do that you could use double quotes to quote your identifiers.

For MySQL: 

SET sql_mode = 'ANSI_QUOTES'

Treat “"” as an identifier quote character (like the “” quote character) and not as a string quote character. You can still use “” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

For MS SQL Server.

SET QUOTED_IDENTIFIER ON

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

Now you can write the single query that works for both, MS SQL Server and MySQL:

UPDATE queries q
SET q."Default" = 0
WHERE q."TYPE" = 4

And before I forget it: best way out of such problems is to avoid reserved words as identifiers, see solution 1. You've got to avoid reserved words of all involved worlds (T-SQL and the sql dialect of MySQL).

Community
  • 1
  • 1
VMai
  • 10,156
  • 9
  • 25
  • 34
  • Thank you for this extensive answer, but is there a way to use one query for both MS SQL and MySQL without the need to SET anything in advance? – Morrtz May 04 '14 at 11:06
  • MS SQL Server: you can set this option for a database with `ALTER DATABASE`, in many cases it will be set automatically by the client. MySQL: if you are the server admin, set it in the configuration file (my.cnf or my.ini). – VMai May 04 '14 at 11:15
  • Above suggestion does not work after running the ANSI_QUOTES for MySQL - 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 'UPDATE queries q SET q."Default" = 0 WHERE q."Type" = 4' at line 3 – Morrtz May 04 '14 at 11:21
  • I've built two fiddles for you: [MS SQL Server](http://sqlfiddle.com/#!3/0fedf/1) and [MySQL](http://sqlfiddle.com/#!2/5f7fd9/1) – VMai May 04 '14 at 11:59
  • Thanks, I will stick with the 2 queries for now. – Morrtz May 05 '14 at 16:05