Update the existing record from the other tables

[mherz715]

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.

 

[Andrzejek]

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

[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
[/pre]

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.

 

[TamarGranor]

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-Step Insert and Update.pdf.

After reading the intro, skip to page 2 for the SQL Server piece of this.

Tamar

 

[Andrzejek]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[TamarGranor]

Yes, definitely meant "NOT properly normalized."

Tamar