CODEWITHSUNDEEP
sqlpostgresql
A. Student
A. Student

Reputation: 57

SQL updating a record in a table concerning another record in the same table

I have a table that contains more than 16,000,000 records. Each record has a primary key (formed by five fields "tsid, plisid, plifc, plisc, dt"), and two counter fields ("icount, aicount").

There is a relation between some of the records in the table.

To simplify the problem let's say we have only these two records

tsid, plisid, plifc, plisc, dt, icount, aicount
10    1       0      0      0   2       2
11    1       0      0      0   7       0

The requirement:

I want to update the "aicount" field in the second record to be 9 (i.e. "icount" in the second record + "aicount" in the first record). The relation between the first and second record is that they have the same values in (plisid, plifc, plisc, dt), and the tsid value of the second record == the tsid of the first record + 1

The desired result after the update is:

tsid, plisid, plifc, plisc, dt, icount, aicount
10    1       0      0      0   2       2
11    1       0      0      0   7       9

I tried this SQL statement in PostgreSQL but I got a syntax error "ERROR: syntax error at or near "SELECT" Position: 59"

UPDATE table1 SET
table1.aicount = table1.icount + SELECT COALESCE( (SELECT CASE
    WHEN table1temp.aicount IS NULL
    THEN 0
    ELSE table1temp.aicount
    END
  FROM table1 table1temp
  WHERE table1temp.tsid = table1.tsid - 1
        AND table1temp.plisid = table1.plisid
        AND table1temp.plifc = table1.plifc
        AND table1temp.plisc = table1.plisc
        AND table1temp.dt = table1.dt), 0)
  WHERE table1.tsid = 10;

What is the wrong in the statement above? Any idea or suggestions?

Upvotes: 1

Views: 180

Answers (2)

IShubh
IShubh

Reputation: 364

Try the following query-:

update T 
set aicount=mm.m
from(
select sum(iCount) over (partition by plisid,plifc,plisc,dt order by tsid) m from T
) mm

SQL Server

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

The error caused because you couldn't use select subquery to add an update column.

You seem to want to get the number, which this row icount number add with last recorded aicount number

I would use LAG function to get last recorded aicount number in subquery then update the number.

There are three parameters in LAG function.

  1. First your column, which you want to get the last column value.
  2. offset from this column value defaults to 1
  3. default value. default to null

lag(value any [, offset integer [, default any ]])

returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

CREATE TABLE T( tsid int, plisid int, plifc int, plisc int, dt int, icount int, aicount int );

INSERT INTO T VALUES (10,1,0,0,0,2,2);
INSERT INTO T VALUES (11,1,0,0,0,7,0);


UPDATE T 
SET aicount = t1.totle
FROM 
(
  SELECT *,(LAG(aicount,1,0) over(order by tsid) + icount) totle
  FROM T
) t1 
WHERE 
T.tsid = t1.tsid 
AND T.plisid = t1.plisid
AND T.plifc = t1.plifc
AND T.plisc = t1.plisc
AND T.dt = t1.dt

Query 1:

SELECT * FROM T

Results:

| tsid | plisid | plifc | plisc | dt | icount | aicount |
|------|--------|-------|-------|----|--------|---------|
|   10 |      1 |     0 |     0 |  0 |      2 |       2 |
|   11 |      1 |     0 |     0 |  0 |      7 |       9 |

Upvotes: 1

Related Questions