I need to update an attribute by copying info from another table. The only problem is to identify the information needed I have to basically follow this format. update the charter table by setting the char_wait_chg equal to the results of selecting the mod_wait_chg from model and aircraft by matching mod_code between model and aircraft tables and ac_number between aircraft and charter tables. I have a Charter table which contains AC_Number and CHAR_WAIT_CHG, the Aircraft table contains AC_Number and MOD_CODE, the Model table contains MOD_CODE and MOD_WAIT_CHG. I need to make CHAR_WAIT_CHG equal to MOD_WAIT_CHG, I thought I could use a subquery, but I get the error ORA-01427: single-row subquery returns more than one row. Here is what I have tried:
UPDATE CHARTER
SET CHAR_TOT_CHG=(SELECT MOD_WAIT_CHG FROM MODEL JOIN AIRCRAFT USING(MOD_CODE) JOIN CHARTER USING(AC_NUMBER));
I hope I explained this well enough, any help would be appreciated.
This is your query. The subquery is returning more than one row:
UPDATE CHARTER
SET CHAR_TOT_CHG = (SELECT MOD_WAIT_CHG
FROM MODEL JOIN
AIRCRAFT
USING(MOD_CODE) JOIN
CHARTER
USING(AC_NUMBER)
);
You have to decide how you want to convert this to one row.
First possibility is that the subquery is wrong (because it is returning all the rows from the three tables).
If you really don't mean to have a different copy of the charter in the subquery, then make it a correlated subquery so you are no returning all rows in all the tables:
UPDATE CHARTER
SET CHAR_TOT_CHG = (SELECT MOD_WAIT_CHG
FROM MODEL JOIN
AIRCRAFT
USING(MOD_CODE)
where charter.ac_number = aircraft.ac_number
);
That might or might not fix the problem. You could still have duplicates and you have to decide what to do with them.
If an arbitrary row is ok, then add:
where rownum = 1
If you want the min()/max()/sum()/avg() of the value, then use:
SET CHAR_TOT_CHG = (SELECT sum(MOD_WAIT_CHG) . . .
If it shouldn't be returning duplicates, then investigate the data some more.