1

I have been given the task to update a few rows in adatabase. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:

update account set currency = 'INR' where id =15;
update account set currency = 'EURO' where id =12;
update account set currency = 'DOLLAR' where id =18;
update account set currency = 'Pound' where id =13; 
-- and so on.

Actually, in this case I was able to do it easily using these update statements because of a relatively low number of rows, but what if there are thousands or lakhs (hundreds of thousands) of rows? Is this the only way to update them, or is there any alternate way or script?

4

You can use a table constructor with VALUES:

update account 
set currency = nv.currency
from
    ( values
        (12, 'EURO'),
        (18, 'DOLLAR'),
        (13, 'Pound')
    ) as nv (id, currency)
where account.id = nv.id ;
2

When you have several thousand rows, it is likely that you already have them in table form, so an UPDATE like this might be possible:

UPDATE account SET currency = temptable.currency 
FROM temptable WHERE id = temptable.id
  • No actually i am having the data in form of excel sheet. Based on the excel sheet data i am updating the records using the update statements. – akhilesh kedarisetty Sep 2 at 11:32
  • 1
    @akhileshkedarisetty You can always import the Excel sheet (after saving it as txt or csv) into the temptable using \copy or COPY. – jjanes Sep 2 at 15:12
1

I would not paste a long list of update statements into an interactive "psql" session, it tends to glitch when lots of stuff is pasted in. You can create file with thousands of update statements like you have there, and then execute the file with psql -f <file.sql>. If there are lakhs of them, you would probably want to either wrap them into a single transaction, or set synchronous_commit=off. Otherwise it might be quite slow. If I have a "one off" job to update many rows from data in Excel, this is what I do.

You could also create a (possibly temporary) table with (id, new_currency), load that table with \copy or COPY from the data you save from your Excel file, and do the cross-table update as others have indicated.

0
WITH cte AS (SELECT 'EURO' currency, 12 id UNION ALL
             SELECT 'DOLLAR'       , 18    UNION ALL
             SELECT 'Pound'        , 13    UNION ALL ... )
UPDATE account 
SET currency = (SELECT cte.currency 
                FROM cte
                WHERE account.id = cte.id)

So you may:

  • do directly like above
  • compact the data into one string of some format (CSV, json) and parse it in CTE using proper function
  • store data into temporary table and use it in UPDATE
  • Can you please explain it with a example? – akhilesh kedarisetty Sep 2 at 11:24
  • FROM UNNEST(ARRAY[12,18,13], ARRAY['EURO','DOLLAR','Pound']) AS source (id, currency) – Akina Sep 2 at 11:36
  • You know that you can use a values clause as a table? Theretno need to use select and union all in Postgres (unlike some ofher DBMSes). – Colin 't Hart Sep 7 at 15:38
-1

You could use the "in" clause to identify multiple rows where the currency can be set to the same value:

update account 
set currency = 'EURO' 
where id in ( 12, 24, 38, 217 ); 

However, this will get slower and slower (and slower) as the list of values grows.

If there is some other way to identify these rows, then use that instead:

update account 
set currency = 'DOLLAR' 
where country = 'USA' ; 
  • I can't use in operator because i am having all unique ID's and Unique values to be updated. – akhilesh kedarisetty Sep 2 at 11:11
  • How many other columns do you have in this table? It might be easier to empty the table and reload it from a text file containing all the values you need. – Phill W. Sep 2 at 11:26
  • There are around 30 columns in this table. So, i think it might be little difficult. – akhilesh kedarisetty Sep 2 at 11:32
  • @akhileshkedarisetty Surely every record doesn't have a unique currency, there are only so many currencies in the world. You could easily use Excel to group the records into sets with the same currency. – jjanes Sep 2 at 15:20
  • I am having doubt about Reindexing. Can anyone help? I can't post this as a question because my questions limit is exceeded. – akhilesh kedarisetty Oct 11 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.