Updating records according to id number 1

[Sashanan]

I'm importing data from one database to another. The previous database used id numbers to identify relations between tables, and I'm trying to get rid of them. I've imported all data, and the situation now is as follows:

Table 1: COMPANY
com_nickname
com_idnumber

Table 2: NICKNAME
nic_nickname
nic_idnumber

What I am trying to do is change all the records in com_nickname to the names listed in table NICKNAME, according to the correct ID number. (At this moment, com_nickname is null in all records, all the other fields have been filled.)

I've tried doing this with an Update query, but found I couldn't use data from both tables in one query that way. Then I tried a nested Update/Select query, but that merely resulted in selecting all records from Nickname, after which SQL was clueless how to put all that information in the appropriate Company records.

Can anybody help me figure out the correct query for this problem? I'd hate to have to update all 3000 records manually...

Thanks in advance!

 

[MikeJones]

If this is Oracle the Following should do the trick...

UPDATE company c1
SET com_nickname = (SELECT n1.nic_nickname
FROM nickname n1, company c2
WHERE c1.com_idnumber = n1.nic_idnumber
AND c2.com_idnumber = c1.com_idnumber)

 

[grega]

This might work ... only guessing though!

update company
set com_nickname = nic_nickname
from company, nickname
where com_idnumber = nic_idnumber

Greg.

 

[Sashanan]

I managed a work-around solution without SQL (wrote a small VB program to do the conversion for me), but thanks for your time anyway!

 

[frag]

A work-around? Why? I think MikeJones solution is the perfect one!

greetings

frag patrick.metz@epost.de

 

[quest4u]

In one of my report, I need to display the duplicate values . Also I need to get a count of certain comparsion between two strings. I had used the following code in a formula to accomplish that task. Now I need to compare only in the distinct records.

Any help would be appreciated.

Thanks.

//@Cnt
WhilePrintingRecords;
Shared NumberVar Cnt;
if {Query.Program Type} in {Query.Pgmarea} then
Cnt := Cnt + 1;

 

[Sashanan]

Solution was perfect, yes, unfortunately I had already gone on without the answer before I checked the forum again...deadlines, you understand

 

[THoey]

Just a suggestion Sashanan (Visitor), if you become a Tek-Tips member, you can get an email notification when someone answers one of your posts.

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[MikeJones]

Quest4u,

What DB is this on?? I'm afraid I'm an Oracle dude and I don't think the code you've posted is Oracle. Perhaps someone else can help you

 

[MalcolmW]

Quest4u,
You would have better luck in the Crystal Reports forum, under a new thread as well... Malcolm

 

[Sashanan]

Thnx for the tip. Feels a little strange calling myself a Programmer when I still have over a year of college to attend, but the option 'IT student' wasn't in the list