CODEWITHSUNDEEP
sql-serversql-update
Rob
Rob

Reputation: 47

Simple SQL update statement from select with undesired results

Below is the statement

UPDATE
  [dbName].[dbo].[tableName]
SET
  [tableName].[columnName] = NULL
SELECT
  [tableName]
FROM [dbName].[dbo].[tableName]
WHERE
  [columnName] like 'match%'

I thought that the only values in columnName beginning with 'match' would be replaced with 'NULL' however, to my surprise all values in columnName were replaced.

Would appreciate some explanation on this.

Upvotes: 0

Views: 158

Answers (1)

strickt01
strickt01

Reputation: 4048

You are running two different SQL statements because you have the syntax wrong:

UPDATE
  [dbName].[dbo].[tableName]
SET
  [tableName].[columnName] = NULL

...and...

SELECT
  [tableName]
FROM [dbName].[dbo].[tableName]
WHERE
  [columnName] like 'match%'

The first UPDATE sets all rows in the table to NULL. The second SELECT then returns all rows where [columnName] like 'match%' (of which there will be none as you've just set them all to NULL).

You want 

UPDATE
      [dbName].[dbo].[tableName]
SET
      [tableName].[columnName] = NULL
WHERE
      [columnName] like 'match%'

The syntax with a FROM clause (but no SELECT) would be:

UPDATE
      [dbName].[dbo].[tableName]
SET
      [tableName].[columnName] = NULL
FROM 
      [dbName].[dbo].[tableName]
WHERE
      [columnName] like 'match%'

...which then allows you to use JOINs etc.

Upvotes: 1

Related Questions