INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update the existing record from the other tables

Update the existing record from the other tables

(OP)
Hi Everyone,

Need your help on this.

I have two tables that I need to identify if there is a data existing from my secondary table and update it on my primary table, and if there is a new record from the secondary, should insert that data too. But there is a problem in my data,
there is a data issue between primary and secondary table. The 'NAME' from secondary table may be differ from the primary but has the same 'ID Number' and vice versa..

How I able to make a such condition that either the two column name has different value for as long as it has one matched record from secondary table to primary table

Here's the sample data:

Primary Table
Name Idnumber Price1 Price2 Price3 Price4
marie 12 123 234.23 0 0
jennifer 13 343 100 0 0

Secondary Table
Name Idnumber Price1 Price2
marie s 12 189 230
jennifer 14 150 125
mery 16 420 500

UPDATE: Primary Table
Name Idnumber Price1 Price2 Price3 Price4
marie 12 123 234.23 189 230
jennifer 13 343 100 150 125
mery 16 0 0 420 500

As you can see the results, this what I want to do.

Need your expertise on this

Thank you in advance.

RE: Update the existing record from the other tables

To show your data this way, please use TGML tags [pre]...[/pre]:

Primary Table 
 Name Idnumber Price1 Price2 Price3 Price4
 marie    12     123  234.23    0     0
 jennifer 13     343  100       0     0

 Secondary Table 
 Name    Idnumber Price   Price2	
 marie s  12       189     230 
 jennifer 14       150     125 
 mery     16       420     500 

 UPDATE: Primary Table 
 Name Idnumber Price1 Price2  Price3  Price4
 marie    12     123   234.23   189     230
 jennifer 13     343   100      150     125
 mery     16       0     0      420     500
 

So it looks to me that the data from Secondary Table's Price and Price2 fields ALWAYS go to Primary Table's Price3 and Price4 fields?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update the existing record from the other tables

First, I have to comment that whenever you have field names ending in digits, you may want to rethink your data structure. That's usually a sign that your tables are properly normalized and down the road, it's likely to get you in trouble.

What you're trying to do is called an upsert. You can use the MERGE command for that. I've written about this here: http://www.tomorrowssolutionsllc.com/Articles/One-.... After reading the intro, skip to page 2 for the SQL Server piece of this.

Tamar

RE: Update the existing record from the other tables

Tamar,
Did you want to say: "That's usually a sign that your tables are NOT properly normalized " ...? ponder

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update the existing record from the other tables

Yes, definitely meant "NOT properly normalized."

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close