CODEWITHSUNDEEP
amazon-web-servicessql-updatecursoramazon-redshiftupdates
Santosh
Santosh

Reputation: 123

Redshift table update using two tables

I have a requirement to work on an update. The requirement is to update table 2 using the data from table 1. Please find below sample records from the two tables:

TABLE A    
-----------------
colA | colB | colC
-----------------
 1    AAA      ABC
 2    BBB      DEF
 3    CCC      GHI
 3    CCC      HIJ

TABLE B    
-----------------
colA1 | colB1 | colC1
-----------------
 1     AAA      
 2     BBB
 3     CCC
 3     CCC

I need to update the colC1 with values of ColC. Expected output is shown below

TABLE B    
-----------------
colA1 | colB1 | colC1
-----------------
 1    AAA      ABC
 2    BBB      DEF
 3    CCC      GHI
 3    CCC      HIJ

Do we need to use a cursor for this or a simple update statement like shown below would do?

 Update table B
 set colC1 = table A.colC
 from TABLE A, TABLE B
 where colA1 = colA
 and colB1 = colB;

Upvotes: 0

Views: 99

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270294

Your SQL seems perfectly fine.

Cursors are normally used for programmatic access to a database, where the program is stepping through the results one-at-a-time, with the cursor pointing to the 'current record'. That isn't needed in normal SQL update statements.

One thing to note... In Amazon Redshift, using an UDPATE on a row causes the existing row to be marked for deletion and a new row is created. (This is a side-effect of using a columnar database.) If many rows are updated, it means that the disk storage becomes less efficient. It can be improved by occasionally running VACUUM tablename, which will remove the deleted storage.

Upvotes: 1

Related Questions