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