SQL to change where data is

[kb178]

I have a table with a First Name column and a Last Name column. 1/2 the records are incorrect where the last names are in the first name column and the first names are in the last name column. I know it's certain records between 2 ids. Is there an easy way to switch the records?
Thanks,
Kristen

 

[dbomrrsm]

There are a number of ways of doing this but I would create two new fields in the table

CORRECT_FIRST_NAME
CORRECT_SURNAME

THEN

UPDATE MYTABLE
SET CORRECT_FIRST_NAME = FirstNameField --from your table
WHERE ID Between (range that you know the first name is in the correct field)

UPDATE MYTABLE
SET CORRECT_FIRST_NAME = SurnameNameField --from your table
WHERE ID Between (range that you know the first name is in the incorrect field)

UPDATE MYTABLE
SET CORRECT_SURNAME = FirstNameField --from your table
WHERE ID Between (range that you know the first name is in the incorrect field)

UPDATE MYTABLE
SET CORRECT_SURNAME = SurnameNameField --from your table
WHERE ID Between (range that you know the surname is in the correct field)

Then you can delete the old name fields and rename the new ones as you like.

DBomrrsm

 

[JamesLean]

Creating extra fields is unnecessary. You can just do:

UPDATE mytable
SET firstname = lastname,
  lastname = firstname
WHERE id BETWEEN 101 AND 201

--James

 

[kb178]

Thanks guys... JamesLean - I read your mind before I read your post! That's exactly how I did it...