Skip to content
Advertisement

UPDATE VALUES OF COLUMN IN POSTGRESQL

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

Advertisement

Answer

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

8 People found this is helpful
Advertisement