Solved

Update Table in SQL 2000

Posted on 2004-10-03
6
308 Views
Last Modified: 2008-03-06
Hi Expert

I have 2 Table Vend01 and Vend02:
In Vend01 I have Phone and Phone1 amount other filed (I do not have PhoneTypeid in this table)
Fields
Phone means Home's Phone Number (People knows by experience)
Phone1 means Office's Phone Number (People knows by experience)

In Vend02 I have Phone and PhoneTypeID
Fields
PhoneTypeID = 1000 means Office's Phone Number
PhoneTypeID = 1001 means Home's Phone Number
Phone means Phone Number Depends on the PhoneTypeID
I need update Phone's Field and PhoneTypeID in Vend02 from Vend01 depending on the criteria that Vend01.Phone means Home's Phone Number(People knows by experience) and Vend01.Phone1 means Office's Phone Number(People knows by experience)
All fields from Vend01 are up to date.

Thanks
0
Comment
Question by:amedexitt
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
If the phone numbers are not already in Vend02, you need to INSERT, not UPDATE:

INSERT Vend02(Phone, PhoneTypeID)
SELECT Phone, 1001
FROM Vend01 v1 LEFT JOIN Vend02 v2 ON v1.Phone = v2.Phone
WHERE v2.Phone IS NULL AND v1.Phone IS NOT NULL

INSERT Vend02(Phone, PhoneTypeID)
SELECT v1.Phone1, 1001
FROM Vend01 v1 LEFT JOIN Vend02 v2 ON v1.Phone1 = v2.Phone
WHERE v2.Phone IS NULL AND v1.Phone1 IS NOT NULL


If the phone numbers are already in Vend02, then you just need to UPDATE PhoneTypeID:

UPDATE v2 SET PhoneTypeID = 1000
FROM Vend02 v2 INNER JOIN Vend01 v1 ON v1.Phone1 = v2.Phone

UPDATE v2 SET PhoneTypeID = 1001
FROM Vend02 v2 INNER JOIN Vend01 v1 ON v1.Phone = v2.Phone
0
 

Author Comment

by:amedexitt
Comment Utility
Every thing looks perfect on the query that you sent to me, there is just one more thing that I forgot. I can't link Vend01 directly with Vend02. In order for me link these table I will need to use another table named Agents, it must be something like this
Agents left outer JOIN
                      Vend02 ON Agents.AgtId = Apven02 .PhoneObjId INNER JOIN
                      Vend01 ON Agents.AgtUfield1 = Vend01.vendno
I will need to use both of them Insert and Update because some of the Phone's number do not exit and some of them do exit. Can you fix this for me please?
AgtUfield1 is the legacy number
Thanks
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
Okay, this is what I now have for your table structures:

Vend01(vendno, Phone, Phone1, amount,...)
Vend02(PhoneObjId, Phone, PhoneTypeID)
Agents(AgtId, AgtUfield1)

What is Apven02?

And when you say that AgtUfield1 is the legacy number, does that mean it's not in Vend01 or Vend02 yet?
0
 

Author Comment

by:amedexitt
Comment Utility
Apvend02 is Vend02
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
Comment Utility
Is this what you mean?

INSERT Vend02(PhoneObjID, Phone, PhoneTypeID)
SELECT AgtId, v1.Phone, 1001 --home
FROM Vend01 v1
 INNER JOIN Agents a ON a.AgtUfield1 = v1.vendno
 LEFT JOIN Vend02 v2 ON v1.Phone = v2.Phone
WHERE v2.Phone IS NULL AND v1.Phone IS NOT NULL

INSERT Vend02(PhoneObjID, Phone, PhoneTypeID)
SELECT AgtId, v1.Phone1, 1001 --office
FROM Vend01 v1
 INNER JOIN Agents a ON a.AgtUfield1 = v1.vendno
 LEFT JOIN Vend02 v2 ON v1.Phone1 = v2.Phone
WHERE v2.Phone IS NULL AND v1.Phone1 IS NOT NULL

UPDATE v2 SET PhoneTypeID = 1000 --home
FROM Vend02 v2
 INNER JOIN Agents a ON a.AgtId = v2.PhoneObjId
 INNER JOIN Vend01 v1 ON a.AgtUfield1 = v1.vendno
WHERE v1.Phone1 = v2.Phone

UPDATE v2 SET PhoneTypeID = 1001 --office
FROM Vend02 v2
 INNER JOIN Agents a ON a.AgtId = v2.PhoneObjId
 INNER JOIN Vend01 v1 ON a.AgtUfield1 = v1.vendno
WHERE v1.Phone = v2.Phone
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now