CODEWITHSUNDEEP
sqlms-accesssql-update
user3165438
user3165438

Reputation: 2661

SQL Simple Update Statement

I have a simple table called t_genopt with 2 columns : Options and AppliesTo.

I get a syntax error when I run the following query: 

UPDATE
    t_genopt
SET
    t_genopt.Option = 'trial'
WHERE
    t_genopt.AppliesTo LIKE 'VCConfigs'

Where is the problematic spot?

Upvotes: 0

Views: 81

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

option is a reserved word in many databases. You want to escape it for this query. The solution is likely to be either:

UPDATE t_genopt
SET [Option] = 'trial'
WHERE t_genopt.AppliesTo LIKE 'VCConfigs';

or

UPDATE t_genopt
SET "Option" = 'trial'
WHERE t_genopt.AppliesTo LIKE 'VCConfigs';

or

UPDATE t_genopt
SET `Option` = 'trial'
WHERE t_genopt.AppliesTo LIKE 'VCConfigs';

The better solution is to avoid using reserved words as column and table identifiers.

Upvotes: 2

Pred
Pred

Reputation: 9042

Depending on the DBMS you are using, the Option keyword could be reserved. Quote the names which are reserved.

In SQL Server and MSAccess:

UPDATE
    t_genopt
SET
    [Option] = 'trial'
WHERE
    t_genopt.AppliesTo LIKE 'VCConfigs'

In MySQL

UPDATE
    t_genopt
SET
    `Option` = 'trial'
WHERE
    t_genopt.AppliesTo LIKE 'VCConfigs'

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Take out the second t_genopt:

UPDATE
    t_genopt
SET
    [Option] = 'trial'
WHERE
    t_genopt.AppliesTo LIKE 'VCConfigs'

Any UPDATE statement can only affect one table, and the table subject to update is identified between UPDATE and SET. There's no need (and you're not allowed in most database systems) to specify the table again on the left side of assignments within the SET clause.

(And, as others have observed, if the column name is Option, as per your example, rather than Options, as per your narrative, you'll need to quote that use)

Upvotes: 0

Related Questions