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 tabledf_t_v_stock_number in temporary table = v_stock_number in primary tableThere are several fields that must be updated if the d_id and v_stock_number match:v_pricev_internet_pricev_other_pricev_mileageAny direction is much appreciated! |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
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 tablesb) Insert statement where the PK values don't exist in the target table2. MERGE statement:Here is a MERGE example:MERGE tbl_Vehicles as tusing tbl_DealerFeed_temp as s on s.d_id = t.d_id and s.df_t_v_stock_number = t.df_t_v_stock_numberwhen 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 OptimizerTG |
|
|
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. |
|
|
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_mileagefrom tbl_Vehicles as tinner 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_numberwhere 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 sfrom tbl_Vehicles as tinner 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 OptimizerTG |
|
|
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? |
|
|
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_priceElseIf v_internet_price <> 0 Then v_search_price = v_internet_priceElseIf v_other_price <> 0 Then v_search_price = v_other_priceElse v_search_price = 0End If This is my ASP code. I would like to be able to do this in the same SQL statement. |
|
|
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.aspxBe One with the OptimizerTG |
|
|
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? |
|
|
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.col1or s.col2 != t.col2or s.col3 != t.col3where not ( s.col1 = t.col1and s.col2 = t.col2and s.col3 = t.col3)Be One with the OptimizerTG |
|
|
|