CODEWITHSUNDEEP
postgresqlsql-update
Sky21.86
Sky21.86

Reputation: 667

Postgresql Update statement

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.

Upvotes: 0

Views: 50

Answers (1)

user330315
user330315

Reputation:

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().

Upvotes: 1

Related Questions