UPDATE VALUES OF COLUMN IN POSTGRESQL

Question

I have 4 tables:

1. matchsal 
ID salaray
1  1000
2  2000
3  3000
4  4000
5  5000

2. TABLE1
ID   C
1   NA
2   NA
3   NA
4   NA
5   NA

3. TABLE2
ID   lfs1   lfs2
1    2      3
2    3      1
3    3      1
4    3      1
5    2      3

4. TABLE3
ID_NIC    filternn
1         private
2         public
3         private
4         Private
5         public

what i want is to update table1 with the salary values from matchsal table with conditions in the others tables, i tried this query:

update TABLE1 LFS
SET  C1= (Select SALARY from matchsal ss )
WHERE LFS."ID" IN
(   SELECT "ID" from
        TABLE2 lfs,
        TABLE3 NIC
    WHERE lfs."ID"=NIC."ID_NIC" 
    and lfs.lfs1 <> LFS.lfs2
    and filternn in ( 'Private'))

and i got this error: ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

No, there id should match with ids in matchsal table, so it should be 1000,3000,4000 — Nora S
– Nora S, Commented Mar 8, 2021 at 19:22

Gordon Linoff · Accepted Answer · 2021-03-08 16:40:54Z

1

The subquery in the SET is causing the problem. It is possible that you want:

SET  C1 = (Select ss.SALARY from matchsal ss where ss.id = lfs.id)

But that is just a guess.

answered Mar 8, 2021 at 16:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Nora S Over a year ago

still face the same problem

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-08 17:00:06Z

1

Try this:

update TABLE1 LFS
SET  C1= (Select ss.SALARY from matchsal ss where lfs.id=ss.id)
WHERE lfs.ID IN
(   SELECT ID from
        TABLE2 lfs,
        TABLE3 NIC
    WHERE lfs.ID=NIC.ID_NIC
    and lfs.lfs1 <> LFS.lfs2
    and filternn in ( 'Private'))

answered Mar 8, 2021 at 17:00

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Comments

Nora S · Accepted Answer · 2021-03-08 19:20:32Z

0

I found the answer ^^" It is simple thing

update TABLE1 LFS
SET  C1= (Select SALARY from matchsal ss where ss.id=LFS."ID" limit 1)
WHERE LFS."ID" IN
(   SELECT "ID" from
        TABLE2 lfs,
        TABLE3 NIC
    WHERE lfs."ID"=NIC."ID_NIC" 
    and lfs.lfs1 <> LFS.lfs2
    and filternn in ( 'Private'))

I Just add a limit to the sub query

edited Mar 8, 2021 at 19:20

answered Mar 8, 2021 at 16:56

Nora S

617 bronze badges

1 Comment

Kazi Mohammad Ali Nur Romel Over a year ago

Don't you have to match ID between matchsal and table1? Otherwise it will pick any salary.

Collectives™ on Stack Overflow

UPDATE VALUES OF COLUMN IN POSTGRESQL

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related