0

I have the same column in multiple tables in my database. I need to update every table that contains that column where the value is equal to 'xxxx'. There's a very similar stack question here which is close to what I'm looking for - I just need to add another condition in my WHERE statement. I'm not sure how to include it in the query as I keep getting syntax errors.

SELECT 'UPDATE ' + TABLE_NAME + ' SET customer= ''NewCustomerValue'' '
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'customer'

The part I'm having problems with is how to include the below line in the 'WHERE' statement.

AND customer='xxxx'
Community
  • 1
  • 1
ovaltein
  • 1,185
  • 2
  • 12
  • 34

2 Answers2

0

Try like this

SELECT 'UPDATE ' + TABLE_NAME + ' SET customer= ''NewCustomerValue'' where customer=''xxxx'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'customer'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

try this:

' AND customer=''xxxx''  ' --(two ' inside a string = ')
Ilan
  • 29
  • 3
  • Would be nice if you explained or included a reference as to why this is required (what two single quotes achieves). – Chad Schouggins May 13 '14 at 20:49
  • 1
    In T-SQL, When you want to include a single quote inside a string, you need to use TWO single quotes. The reason for that is to allow SQL Server to identify that this is not a piece of code but a string value. – Ilan May 16 '14 at 23:23