0

I'm a rather newbie when it comes to SQL queries and not sure how to approach this: I have a CSV file that contains 5 columns, 2 of those columns are Value1 and Value2, I need to run over an existing sql table (for this question's purposes I'll call it "target table") and iterate over all rows in target table checking their Value1 column, if that Value1 content equals to the one in the CSV I need to insert Value2 into the Value2 column of that row if the Value1 is not contained in the table, create a new row for it.

Just in case I wasn't clear, here's an example -

assuming the CSV looks like the following:

Name, Age, Location, Height, Weight
David, 12, Macedonia, 1.87, 96
Kim, 15, Denmark, 1.95, 67

I want to go over the existing SQL and work according to name and weight only - if the name David is in the table, insert 96 to its Weight column, if the name Kim is in the table, insert 67 to its Weight column etc... If the table only contained Kim and not David, then the David row would be created.

I'm assuming the wise way would be to first fill in the gaps of "Value1" that aren't existing in the table and only then run an update on the "Value2" but I might be wrong.

Any help would be much appreciated, thanks!

Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
user51929
  • 185
  • 1
  • 15
  • 1
    The first step here is going to be to probably use `LOAD DATA` to bring your CSV file into MySQL. Then, worry about the query for your update. – Tim Biegeleisen Apr 18 '18 at 10:51
  • 1
    You can set Value1 as unique key in table. Then use 'insert ignore into yourtable ..... on duplicate key update .....' construct. – Yuri Lachin Apr 18 '18 at 11:06

1 Answers1

1

Theoretically, I think this should work for you.

--Part 1: Clear/Create temporary table and Load CSV into SQL. Credit to mr_eclair for describing this process here

drop table #temp

create table #temp (
tName nvarchar(25),
tAge int, 
tLocation nvarchar(25),
tHeight float(3,2), -- alternatively, use cm instead of m and just use int(3)
tWeight int
)

BULK INSERT #temp
FROM 'C:\CSVData\updates.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',  --CSV field delimiter
ROWTERMINATOR = '\n',   --Use to shift the control to next row
TABLOCK
)

--Part 2: Setting a Unique Key; as suggested by @Yuri_Lachin

Alter table target
Add Unique (Name) -- Sets Name column as a Unique Key for the table target

--Part 3: Adding rows and Updating values from temp table to permanent table. Credit to MySQL 5.7 Reference Manual 13.2.5.2

Insert into target(Name, Age, Location, Height, Weight)
Select tName, tAge, tLocation, tHeight, tWeight from #temp
On DUPLICATE KEY Update Weight = tWeight

I was going to suggest using a Merge statement like the following, but it looks like MySQL doesn't deal with those.

Merge Into people
using #temp
on target.name = #temp.tname
when matched then Update
set target.weight = #temp.tweight
when not matched then 
Insert (target.name, target.age, target.location, target.height, target.weight)
values (#temp.tname, #temp.tage, #temp.tlocation, #temp.theight,  #temp.tweight);
Andrew Edwards
  • 65
  • 5
  • Thank you very much, this worked perfectly, the only thing I find a bit funny is that when writing Weight = tWeight I actually need to have the columns named differently in each table instead of being able to do something like destination.Weight = source.Weight or something of that manner :), again thanks for the assistance ! – user51929 Apr 24 '18 at 11:49