Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update One Table From Another Using Primary Keys

Author  Topic 

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 16:04:03
I have two tables:

tbl_Vehicles (primary table)
tbl_DealerFeed_temp (temporary table)

I load values from a TXT file into the temporary table. From there, I figure out which records are new and need to be added to the primary table, and which records are duplicates, and need to a) update the primary table and b) be deleted from the temporary table.

Basically, I need to write a SQL statement to grab the records that exist in BOTH tables based upon two primary criteria:

d_id in temporary table = d_id in primary table
df_t_v_stock_number in temporary table = v_stock_number in primary table

There are several fields that must be updated if the d_id and v_stock_number match:

v_price
v_internet_price
v_other_price
v_mileage

Any direction is much appreciated!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-17 : 16:08:51
I'd suggest a MERGE statement:
http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 16:16:37
I just typed all this up before I say Lamprey's response so I'll post anyway

Two methods will work:
1.
a) Update statement with inner join on the PK values which will only work on rows that are in both tables
b) Insert statement where the PK values don't exist in the target table

2. MERGE statement:

Here is a MERGE example:

MERGE tbl_Vehicles as t
using tbl_DealerFeed_temp as s
on s.d_id = t.d_id
and s.df_t_v_stock_number = t.df_t_v_stock_number
when not matched by target then
insert (d_id
,df_t_v_stock_number
,v_price
,v_internet_price
,v_other_price
,v_mileage)
values (s.d_id
,s.df_t_v_stock_number
,s.v_price
,s.v_internet_price
,s.v_other_price
,s.v_mileage)
when matched and not
( t.v_price = s.v_price
and t.v_internet_price = s.v_internet_price
and t.v_other_price = s.v_other_price
and t.v_mileage = s.v_mileage)
then update set
t.v_price = s.v_price
,t.v_internet_price = s.v_internet_price
,t.v_other_price = s.v_other_price
,t.v_mileage = s.v_mileage;


Be One with the Optimizer
TG
Go to Top of Page

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 16:41:08
I don't need to INSERT any records in this statement. I will only need to UPDATE. I have another SQL statement that will INSERT new records. Here is where I have gotten but am stuck on how to proceeed.


		strSQL = "UPDATE tbl_Vehicles v SET " & _
"v.v_price = dft.df_t_v_price, " & _
"v.v_internet_price = dft.df_t_v_internet_price, " & _
"v.v_other_price = dft.df_t_v_other_price, " & _
"v.v_mileage = dft.df_t_v_mileage " & _
"WHERE .......
"LEFT JOIN tbl_DealerFeed_temp dft ON ......


Also, once the vehicle has been updated in the primary table, I will need to delete the record in the temporary table.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 16:48:25
[code]
update t set
t.v_price = s.v_price
,t.v_internet_price = s.v_internet_price
,t.v_other_price = s.v_other_price
,t.v_mileage = s.v_mileage
from tbl_Vehicles as t
inner join tbl_DealerFeed_temp as s
on s.d_id = t.d_id
and s.df_t_v_stock_number = t.df_t_v_stock_number
where not ( t.v_price = s.v_price
and t.v_internet_price = s.v_internet_price
and t.v_other_price = s.v_other_price
and t.v_mileage = s.v_mileage)

delete s
from tbl_Vehicles as t
inner join tbl_DealerFeed_temp as s
on s.d_id = t.d_id
and s.df_t_v_stock_number = t.df_t_v_stock_number
[/code]

Be One with the Optimizer
TG
Go to Top of Page

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 16:50:10
TG, thank you! What is the WHERE NOT statement doing in this statement?
Go to Top of Page

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 16:52:47
Also, once final question regarding this statement. I have a field called v_search_price in the primary table. I need this price to be updated in the following order:

If v_price <> 0 Then
v_search_price = v_price
ElseIf v_internet_price <> 0 Then
v_search_price = v_internet_price
ElseIf v_other_price <> 0 Then
v_search_price = v_other_price
Else
v_search_price = 0
End If


This is my ASP code. I would like to be able to do this in the same SQL statement.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 16:58:51
>>What is the WHERE NOT statement doing in this statement?
Really? No clue at all? Sqlteam.com helps those who help themselves

Books Online - CASE:
http://technet.microsoft.com/en-us/library/ms181765%28v=sql.110%29.aspx

Be One with the Optimizer
TG
Go to Top of Page

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 17:03:54
I understand the WHERE NOT but I don't understand why you are using it in that context. Why are you checking the field values in one table against the field values in the other? And why use the AND operator? If anything, shouldn't it be OR to check if any of the four values are different? Is that checking if those values are not equal? If so, update?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-17 : 17:21:10
In your case it may not be important. All it is doing is preventing an unnecessary update for a row if all the values already match.
The parenthesis is important here. Logically these two WHERE clauses are equivalent:

where s.col1 != t.col1
or s.col2 != t.col2
or s.col3 != t.col3

where not (
s.col1 = t.col1
and s.col2 = t.col2
and s.col3 = t.col3
)

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -