Postgresql Update statement

Question

I am currently operating with 2 tables: one is a live one and one is a stage one. Above code updates values in the live table using staging table as a source. It only updates values in column "firstname" if the row in the stage table already exists in the live table and some other simple criteria.

Update LiveTable
    SET LiveTable.firstname = TestTable.firstname
FROM TestTable
    WHERE EXISTS (SELECT 1 FROM LiveTable WHERE LiveTable.userid = TestTable.userid)
    AND TestTable.firstname IS NOT NULL
    AND LEN(TestTable.firstname) > len(LiveTable.firstname);

Above code jets the job done but takes quite some time. I was wondering if there is any faster way to do it.

I have tries to create FUNCTION to do the same thing, but was not able to get it to work.

Try where livetable.userid = testtable.userid instead of the where exists (...) — user330315
– user330315, Commented Jan 20, 2016 at 8:34

user330315 · Accepted Answer · 2016-01-20 08:36:49Z

1

Use a join between the two tables

Update LiveTable
    SET LiveTable.firstname = TestTable.firstname
FROM TestTable
WHERE LiveTable.userid = TestTable.userid
  AND TestTable.firstname IS NOT NULL
  AND length(TestTable.firstname) > len(LiveTable.firstname);

The condition TestTable.firstname IS NOT NULL is not really needed because length(TestTable.firstname) > len(LiveTable.firstname) will filter out rows where firstname is null anyway. And it should be length() not len().

answered Jan 20, 2016 at 8:36

user330315

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

2 Comments

Sky21.86 Over a year ago

Thanks alot @a_horse_with_no_name It did take me some time to test this bit but it does the job exactly how i want it. Something that used to take a few hours is no taking a few minutes. Is there a reason why this bit is much faster then a regular 'WHERE EXISTS (SELECT 1 from ....)' statement? Is there any impact on stability/reliability of the outcome compared to using 'WHERE EXISTS (SELECT 1 from ....)'?

user330315 Over a year ago

@Sky21.86: the exists co-related subquery will be executed once for each row in livetable if TestTable.userid isn't indexed, this will be extremely expensive. The join reads both tables only once (although probably all rows from testtable). Processing many rows in "one go" is usually faster than doing a row-by-row processing. But you need to check the execution plan to verify this.

Collectives™ on Stack Overflow

Postgresql Update statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related