CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-server-2012
user3820933
user3820933

Reputation: 27

Handling column names with spaces in SQL Server

I have created a SQL Query to update various columns, where the column names contain spaces. It works if I run it manually as a query:

UPDATE dbo.Survey
SET PhotoPathQ1='(null)'
WHERE "Q1 Photo Taken"='0'

UPDATE dbo.Survey
SET PhotoPathQ2='(null)'
WHERE "Q2 Photo Taken"='0'

UPDATE dbo.Survey
SET PhotoPathQ3='(null)'
WHERE "Q3 Photo Taken"='0'

... and further similar updates

However if I try to automate this using SQL Server Agent as a Transact-SQL script (T-SQL) it does not actually do anything to my table, the job says that it has run successfully but the data has not been updated.

Any help would be appreciated.

Am I missing something obvious with this?

Upvotes: 2

Views: 7459

Answers (1)

Tanner
Tanner

Reputation: 22733

Looks like an error in your syntax, try this as an example:

UPDATE dbo.Survey
SET PhotoPathQ1 = null
WHERE [Q1 Photo Taken] = 0

This assumes that the field PhotoPathQ1 is nullable and you actually want to insert a true null value in to it rather than a string '(null)'.

It also assumes that [Q1 Photo Taken] is a bit or int field, although SQL Server will handle the conversion happily if you have it in quotes. If it's a string data type, then you should leave the quotes there.

You should use square brackets on field names that contain spaces instead of double quotes:

[Q1 Photo Taken]

Upvotes: 4

Related Questions