How can I update a column in table A according to a column in table B? & alpha formatting

Question

Post reply

How can I update a column in table A according to a column in table B? & alpha formatting

Henry Li

SSC Veteran

Points: 213
More actions
January 29, 2006 at 9:17 pm

#167284

Hello All,

Can anybody tell me how to update a table's whole column according to another table's column? I want to update all the column, not just one or two rows.

I have table A with LastName, FirstName, and OldEmail fields;

table B with LastName, FirstName, and NewEmail.

So I tried the following query but doesn't work, How can I write the right one?

Update Table A

Set TableA.OldEmail = TalbeB.NewEmail

where TableA.LastName = TableB.LastName and

TableA.FirstName = TableB.FirstName

Or do I need to join these two tables before update them? Any hint?

By the way, I have another question on cell formatting for varchar.

I got one column of varchar data which are phone numbers, but they're with the US style (012)345 6789, I want to change them to the correct style (01)2345 6789 so as to avoid misunderstanding for users. How?

Do I use Alter Table Modify(ColumnA, XXX) to do it? I have no idea how to set it.

Thank you very much for any of your recommendations!

Henry

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ajay_k73 SSC Veteran Points: 217 More actions · Answer 1

ajay_k73

SSC Veteran

Points: 217

January 29, 2006 at 10:11 pm

#617661

you try this query I think it should work

update TableA

Set TableA.OldEmail = TableB.NewEmail

from TableA join TableB on

TableA.LastName = TableB.LastName and

TableA.FirstName = TableB.FirstName

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

If you're sure that all your phone #s are valid, correct, of fixed length & formatted the exact same way in all the rows you could do some string manipulation to either store it the way you want (you would also change the application interface that sends this pre-formatted string to the database) or just strip the parantheses and add them as string literals during display...to clarify..this is one way of doing it...

you could update that column by doing a...

SELECT REPLACE(REPLACE('(012)345 6789', ')', ''), '(', '')

and then display it with...

SELECT '(' + LEFT('012345 6789', 2) + ')' + RIGHT('012345 6789', 9)

...everything hinges on all the "IFs" that're outlined in the beginning...

**ASCII stupid question, get a stupid ANSI !!!**

Henry Li SSC Veteran Points: 213 More actions · Answer 3

Hello Ajay_73,

I find the code below worked for the updating column according to columns in another table.

Update TableA

Set TableA.OldEmail =

(Select TableB.NewEmail

from TableB

where TableA.LastName = TableB.LastName and

TableA.FirstName = TableB.FirstName)

Where exists

(select TableB.NewEmail

from TableB

where TableA.LastName = TableB.LastName and

TableA.FirstName = TableB.FirstName)

The "where exists" is just to avoid updating those in TableA which don't have a match in TableB.

Thanks!

Henry

Henry Li SSC Veteran Points: 213 More actions · Answer 4

Hello Sushila,

Thanks a lot for your hints!

My old data column has some phone numbers with one kind of wrong format, and also includes some correct formatted numbers as well.

The wrong format is like this:

Phone

(61) 039 552-1100

(60) 048 223-0088

So they have two kind of charateristics, one is with a hyphen before the last four digit, the other is with a country code of 61 or 60. They need to be changed to (61) 03 9552 1100, or (60) 04 8223 0088.

So I might need some if..then to select them out before changing them.

By the way, what does the function of Select(replace()) do?

I am trying your codes now.

Thanks a lot for your help!

Henry

Henry Li SSC Veteran Points: 213 More actions · Answer 5

Hello Sushila,

You are really an expert!

I've done the codes according to your hints. The final code is like below:

Update DBCJW.dbo.Client_Tbl

Set Phone_1 =

(Case when (len(Phone_1)>14 and left(right(Phone_1,5),1)='-'

and left(Phone_1,4) in ('(61)', '(60)'))

then

Left(REPLACE(REPLACE(Phone_1, ' ', ''), '-', ''), 4) + ' '

+ right(Left(REPLACE(REPLACE(Phone_1, ' ', ''), '-', ''), 6),2) + ' '

+ left(right(REPLACE(REPLACE(Phone_1, ' ', ''), '-', ''),8),4) + ' '

+ Right(REPLACE(REPLACE(Phone_1, ' ', ''), '-', ''), 4)

else Phone_1

end)

Thank you very much for your help & Have a good day!

Henry

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

sushila

SSC-Dedicated

Points: 35293

February 1, 2006 at 6:44 am

#618092

Henry - glad you were able to work it out...as for updating the email addresses - you don't need a separate "where exists..."....just add "and TableB.NewEmail is not null" to your existing where clause!!!

**ASCII stupid question, get a stupid ANSI !!!**

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 7

Michael E. Baker

Say Hey Kid

Points: 676

February 3, 2006 at 12:25 pm

#618774

Henry,

Here's a way to do the first part of the question using a join and making sure you don't overwrite OldEmail with a NULL as Sushila just pointed out:

UPDATE A

Set A.OldEmail = B.NewEmail

FROM TableA A INNER JOIN TableB B ON

A.LastName = B.LastName AND

A.FirstName = B.FirstName AND

B.NewEmail Is Not Null