Changed one field in SQL database

• T Oliver

  Valued Member

  Points: 64

  More actions

  October 20, 2006 at 9:25 am

  #170840

  I guess this is a really simple question, but I'm only a day old on my first SQL database.

  I have a column (the third one) on a database that simply records an integer. I need to script a process where every entry in this column will be checked and if an 8 is found, then change it to a 9.

  I haven't tried yet for fear of trashing 50000 entries already but I figure the following may work?

  UPDATE dbo.mytable SET

  col3 = REPLACE(col3, '8', '9')

  I guess there is probably a much better way to simply search through one column of a table and change any 8's to 9's.

  Can anyone assist please?

  Thanks

  Been doing some more reading. How about

  UPDATE dbo.mytable SET column3 = 9 WHERE column3 = 8

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  October 20, 2006 at 9:37 am

  #667109

  Your update statement will work fine.  You could also use

  UPDATE dbo.mytable

  SET col3 = '9'

  WHERE col3 = '8'

  Greg

  Greg

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 20, 2006 at 9:53 am

  #667123

  Greg's example is actually a bit better;

  your example script would work, but it would always say 50000 rows affected, as there is no where clause. with no where clause, it affects every row, even though it might not change every row.

  Greg's is better because it only affects the rows that have the value '8',

  so if you ran it twice in a row, it might say 1277 rows(s) affected and then 0 rows(s) affected.

  if you ran it a month later, after more data is inserted, it would only affect  new data that had the wrong value.

  a very handy way to check your results, without screwing up the database, is to use a transaction, review the results, and either rollback or commit the transaction after reviewing the changes.like this:

  SET XACT_ABORT ON

  --see the items to be affected

  SELECT * FROM DBO.MYTABLE WHERE col3 = '8'

  BEGIN TRAN

  UPDATE dbo.mytable

  SET col3 = '9'

  WHERE col3 = '8'

  --confirm the data looks like it should

  SELECT * FROM DBO.MYTABLE WHERE col3 = '8'

  --now if you like the results you've reviewed, you COMMT, otherwise, you ROLLBACK:

  --highlight one of the two commands and run it:

  --  ROLLBACK TRAN

  --  COMMIT TRAN

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• T Oliver

  Valued Member

  Points: 64

  More actions

  October 20, 2006 at 9:59 am

  #667127

  Thanks to you both for the very quick response. What you have both said worked perfectly. I like the 'try it first' method Lowell. I'll remember that.

  I've got a lot to learn, but its great to have you guys help on here. Thanks