SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update One Table From Another Using Primary Keys
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BLarche
Starting Member

21 Posts

Posted - 04/17/2014 :  16:04:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/17/2014 :  16:08:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  16:16:37  Show Profile  Reply with Quote
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 - 04/17/2014 :  16:41:08  Show Profile  Reply with Quote
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.

Edited by - BLarche on 04/17/2014 16:42:32
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  16:48:25  Show Profile  Reply with Quote

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


Be One with the Optimizer
TG
Go to Top of Page

BLarche
Starting Member

21 Posts

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

BLarche
Starting Member

21 Posts

Posted - 04/17/2014 :  16:52:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  16:58:51  Show Profile  Reply with Quote
>>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 - 04/17/2014 :  17:03:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/17/2014 :  17:21:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000