Solved

Update Table in SQL 2000

Posted on 2004-10-03
6
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12214363
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
ID: 12214522
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
ID: 12216168
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
ID: 12216493
Apvend02 is Vend02
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 12216855
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error 438 6 52
Access Schema Relationship Design for Multi-Level Chart of Accounts 17 80
sql views 3 55
I really need your help with access query 9 28
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

734 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