Update Column based on Bulk insert from CSV SQL

Question

I am trying to update a table based on the CSV file. I have got list of Id's and trim number in CSV and I need to update the Trim number based on the specific id's in the SQL table.

So far the script I have written

-- Create a temporary table for the import
   drop table #InterpreterTrimNumbers
   CREATE TABLE #InterpreterTrimNumbers( 
  [SAPInterpreterId] int,
  TempTrimNumber varchar(100)
)

BEGIN TRANSACTION
  -- Bulk import into the temporary table
  BULK INSERT #InterpreterTrimNumbers FROM 'C:\CSVData\Trim numbers.csv' WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    ERRORFILE = 'C:\CSVData\Trim numbers-errors.csv',
    TABLOCK
  )

  update Interpreters set TrimNumber = (select TempTrimNumber from #InterpreterTrimNumbers where #InterpreterTrimNumbers.SAPInterpreterId = Interpreters.SAPInterpreterID )
  Commit Transaction

This one returns 1824 rows affected even thought the CSV file has only got 110 records. I only want to update the record with specific SAPInterpreterID

Any help is appreciated.

I am just curious .. Even the query that I executed updates the relevant records and seem to be working fine. But it displayed the incorrect number of records updated. Do you have any idea why this is happening ? — Arun Kumar
– Arun Kumar, Commented May 29, 2015 at 5:18
Actually my CSV file has got sub set of records from the actual table. And it is one to one. But when I ran my query on the interpreter which were not in the csv but has trim number in SQL table. Noticed that there trim number was set to null by running that query. It could have screwed the whole thing on the live server. You saved my life .. Cheers :) — Arun Kumar
– Arun Kumar, Commented May 29, 2015 at 5:47
Glad it helped. This makes sense now. The syntax that you have will update every single Number. The query after the = would select an id if there is a match and will select NULL if there was no match. Therefore, updating records that don't have a match to null. — BICube
– BICube, Commented May 29, 2015 at 6:04

BICube · Accepted Answer · 2015-05-29 06:06:46Z

6

Assuming that the you only get 110 records inserted into your temp table and that IDs are unique in the Interpreters table, then this how your syntax should look like

UPDATE i 
SET TrimNumber = tempintr.TempTrimNumber
FROM Interpreters AS i INNER JOIN 
     #InterpreterTrimNumbers tempintr
     ON i.TrimNumber= tempintr.TempTrimNumber

The reason your query didn't work because it was trying to update every single number in the Interpreters table. When there is a match withe temp table, then yes this is what you needed. But when there is no match, your query would return NULL causing non-matched records to be updated to NULL.

edited May 29, 2015 at 6:06

answered May 29, 2015 at 4:40

BICube

4,7211 gold badge26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DCCoder · Accepted Answer · 2020-10-13 11:03:01Z

0

df10 = pd.read_csv("/tmp/z34.csv")

iters = df10.iterrows()


    for index, row in iters:
        conn = psycopg2.connect(host=rds_host,
                           database=db_name,
                           user=name,
                           password=password)
   
    cur.execute(
                'UPDATE "master_brand" SET  "description" = %s, "created_at" = %s, 
                 "updated_at" = %s WHERE "master_brand"."brand_name" = %s',
                 [row['description'], row['created_at'], row['updated_at'],
                 row['brand_name']])
   
    conn.commit()
   
    conn.close()

edited Oct 13, 2020 at 11:03

DCCoder

1,6784 gold badges21 silver badges31 bronze badges

answered Oct 12, 2020 at 19:21

Amit Tiwari

5031 gold badge5 silver badges5 bronze badges

Collectives™ on Stack Overflow

Update Column based on Bulk insert from CSV SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related