Update multiple columns

[Zojak_Quaguz]

Table "people" has two columns: ID_NUMBER varchar(5), NAME varchar(20). Sample rows:

 

1 John

2 Steve

3 Suzy

4 Janet

 

Table "relationships" has two columns: PERSON1 varchar(20), PERSON2 varchar(20). Sample rows:

 

1 2

1 3

1 4

2 3

2 4

3 4

 

I want to replace the numbers in table "relationships" with the matching names from table "people".

 

I tried

 UPDATE people p, relationships r SET r.PERSON1 = p.NAME WHERE r.PERSON1 = p.ID_NUMBER;

 

which worked fine, then I tried to update the second column with

 UPDATE people p, relationships r SET r.PERSON2 = p.NAME WHERE r.PERSON2 = p.ID_NUMBER;

 

and nothing happened.

 

Thoughts?

[Adam]

Why are you actually updating this table in the first place? You should store the user's ID in a table like that, not data that could possibly change in the future.

[Zojak_Quaguz]

I know, but that's what I was asked to do

[Adam]

Fair enough. Is an error returned, or is the affected rows just 0?

[PFMaBiSmAd]

Your second query works for me. I recommend that you try again and if necessary fetch the actual table contents after you execute the query to see the changes.

[Zojak_Quaguz]

MrAdam: Affected rows 0.

 

PFMaBiSmAd: Weird. I did that, and the second row showed up as NULL just as before.

[PFMaBiSmAd]

You would need to show us what result you are seeing in front of you for anyone to be able to help with it.

 

Best guess is some of your data values contain white-space/non-printing characters and the query does not match them.