2

I'm trying to update a single column in a table for many rows, but each row will have a different updated date value based on a unique where condition of two other columns. I'm reading the data from a csv, and simply updating the date column in the row located from the combination of values in the other two columns.

I've seen this

SQL update multiple rows based on multiple where conditions

but the SET value will not be static, and will need to match each row where the other two column values are true. This is because in my table, the combination of those two other columns are always unique.

Psuedocode

UPDATE mytable SET date = (many different date values)
WHERE col_1 = x and col_2 = y

col_1 and col_2 values will change for every row in the csv, as the combination of these two values are unique. I was looking into using CASE in postgres, but I understand it cannot be used with multiple columns.

So basically, a csv row has a date value, that must be updated in the record where col_1 and col_2 equals their respective values in the csv rows. If these values don't exist in the database, they are simply ignored.

Is there an elegant way to do this in a single query? This query is part of a spring batch job, so I might not be able to use native postgres syntax, but I'm struggling to even understand the format of the query so I can worry about the syntax later. Would I need multiple update statements? If so, how can I achieve that in the write step of a spring batch job?

EDIT: Adding some sample data to explain process

CSV rows:

date,        col_1,    col_2
2021-12-30,  'abc',    'def'
2021-05-30,  'abc',    'zzz'
2021-07-30,  'hfg',    'xxx'

I'll need my query to locate a record where col_1='abc' AND col_2=def, then change the date column to 2021-12-30. I'll need to do this for every row, but I don't know how to format the UPDATE query.

GMB
  • 216,147
  • 25
  • 84
  • 135
user7392939
  • 23
  • 1
  • 8

2 Answers2

1

You can insert your CSV data into a (temporary) table (say mycsv) and use UPDATE with a FROM clause. For instance:

CREATE TEMP TABLE mycsv (date DATE, col_1 TEXT, col_2 TEXT);
COPY mycsv FROM '/path/to/csv/csv-file.csv' WITH (FORMAT csv);
UPDATE mytable m SET date = c.date 
FROM mycsv c WHERE m.col_1 = c.col_1 AND m.col_2 = c.col_2;
clemens
  • 16,716
  • 11
  • 50
  • 65
  • Thanks, I ended up using something similar to, but not exactly like this second answer here https://stackoverflow.com/questions/7019831/bulk-batch-update-upsert-in-postgresql I create a temporary data table and just map all the csv values using unnest which is a java.sql.Array. – user7392939 Oct 23 '19 at 13:14
1

Create an Itemwriter implementation, and override the write() method. That method accepts a list of objects, each returned from your ItemProcessor implementation.

In the write method, simply loop through the objects, and call update on each one in turn.

For Example:

In the ItemWriter: 

 @Autowired
 private SomeDao dataAccessObject;

 @Override
 public void write(List<? extends YourDTO> someDTOs) throws Exception {

   for(YourDTO dto: someDTOs) {
      dataAccessObject.update(dto);
   }
 }

In your DAO:

private static final String sql = "UPDATE mytable SET dateField = ? WHERE col_1 = ? and col_2 = ?";

public void update(YourDTO dto) {

   Object[] parameters = { dto.getDate(), dto.getCol1(), dto.getCol2()};
   int[] types = {Types.DATE, Types.STRING, Types.STRING}; 

   jdbcTemplate.update(sql, parameters, types); 
}
Michael Peacock
  • 2,011
  • 1
  • 11
  • 14
  • Well I'm using individual tasklets instead of readers and writers, so I just have to put everything in the execute() method for building and executing the query. – user7392939 Oct 23 '19 at 13:15
  • Also, this update would only work for a single row no? – user7392939 Oct 23 '19 at 13:20
  • > Well I'm using individual tasklets instead of readers and writers, so I just have to put everything in the execute() method for building and executing the query. Please post your code so we can help further. > Also, this update would only work for a single row no? No - the update would occur once for each item passed into the writer – Michael Peacock Oct 23 '19 at 18:36
  • I was trying to avoid a batch update, and just send a single large update query to the DB for performance reasons. – user7392939 Oct 24 '19 at 14:17
  • > I was trying to avoid a batch update, That's kind of the point of Spring Batch tho – Michael Peacock Oct 24 '19 at 19:37
  • I worded that weird, I meant a batch of individual updates. I've since figured it out using temporary tables in Postgres, but you had the right idea. Thanks! – user7392939 Oct 25 '19 at 02:35