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