Update a column using a while loop

Question

Hi guys, the Newbie here

I have 2 table

Table 1 ( Owner Table) has the following columns: CorpName , FirstName , LastName, MiddleInitial , ADDRESS1, CITY, STATECODE, ZIPCODE, CellPHone , Email

Table 2 (Contact Table) Has the following columns : FirstName, LastName, Address, City, State, Zip, CellPhone, EMail

Some of the FirstName, LastName columns in table 1 are empty.

I need to write a loop that will go through the empty names and using a left outer join will update the owner table with FirstName, LastName, CellPhone, Email
From the contact table, when there is an address match.

I have no idea how to loop in sql.

Any help is appreciated.

Answer 1

You don't write loops in SQL very often. Normally, you try to do things in a single statement. In this case, this could be:

UPDATE OwnerTable
SET   FirstName = isnull(O.FirstName, C.FirstName),
      LastName = isnull(O.LastName, C.LastName),
      CellPhone = isnull(O.CellPhone, C.CellPhone),
      Email = isnull(O.Email, C.Email)
FROM  OwnerTable O
JOIN  Contacttable C ON O.Adderss = C.Address
                         AND O.City = C.City
                         AND O.State = C.State
                        AND O.Zip = C.Zip
WHERE O.FirstName IS NULL OR 
     O.Lastname IS NULL OR
     O.CellPhone IS NULL OR
     O.Email IS NULL

I've written the query so that if some columns already have values, they are retained.

Answer 2

How one table is related to another? You would not need loop for that update assuming tables do not have too many (millions) rows. It can be done in a single update command assuming there is a common column that can be used to match these two tables.

Answer 3

Do you write if then statements in SQL ?

for example lets assume that in the contact table i have 2 phone numbers for the same owner
in VB / VC i can write an IF - Then statement that if phone1 exist and

FirstName & LastName are the same then enter phone2 into its column

But how do you do so in SQL ?

That would depend on the table structure, which is not clear to me in this example.

Generally, when you ask a question, it is a good idea to provide CREATE TABLE statement for your table(s) and INSERT statements with sample data, together with the desired result given the sample. This helps to clarify your question and makes it easy to copy and paste into a query window.

If you want to pursue this question, I think it is better to start a new thread.