SQL更新语句，默认为列名

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.

我有一个SQL代码来更新列中的值。我需要找到一个适用于MS SQL和MySQL的查询。主要问题是我使用的列是Default和Type,它们是SQL中保存的名称,因此它不能与普通的更新语句一起使用。

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!

非常感谢您的帮助!

1 个解决方案

#1

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

您必须为两台服务器启用ANSI引号。如果您这样做,您可以使用双引号来引用您的标识符。

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.

将“”“视为标识符引号字符(如”引号字符“)而不是字符串引号字符。在启用此模式时,您仍然可以使用”“引用标识符。启用ANSI_QUOTES后,不能使用双引号引用文字字符串,因为它被解释为标识符。

For MS SQL Server.

对于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.

导致SQL Server遵循关于引号分隔标识符和文字字符串的ISO规则。由双引号分隔的标识符可以是Transact-SQL保留关键字,也可以包含Transact-SQL语法规则通常不允许的字符。

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

现在,您可以编写适用于MS SQL Server和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).

在我忘记它之前:解决这些问题的最好方法是避免将保留字作为标识符,请参阅解决方案1.您必须避免所有相关世界的保留字(T-SQL和MySQL的sql方言)。

#1