More than one row returned by a subquery in PostgreSQL

In the table1 there is a column named calendar_date, the record is in the format 10/8/2010, in the table2 there is a column named date, with the format of 10/8. In the table2 there are other two columns named daynoleap, dayleap, which indicates the julian date number of either noleap year or leap year. Now I need to add one column of these two to table1, determined by the year of the column calendar_date. If it is 2010, it is devided by 4 and has a remainder, so I add the daynoleap column of table2 to the julian_date column of table1. Otherwise I add dayleap column to it.

I am getting an error: more than one row returned by a subquery used as an expression, by using the codes below: (I believe the error comes from the query statement for now). All the codes are within a forloop, in which I got single record of the variable of year(e.g.2010), monthDate(e.g.10/8).

      while int(year)%4 == 0:
            statement2="UPDATE table1 SET julian_date = (SELECT dayleap FROM table2 WHERE date = '%s') WHERE (SELECT date FROM table2) = '%s'"
            statement2=statement2 % (monthDate, monthDate)
            curs2 = conn.cursor()
            curs2.execute(statement2,)
            conn.commit()

obviously there is something wrong with the codes to prevent it from getting single record to update. But since I already have a WHERE clause for the update statement, and I don’t see problem with the statement neither. I tried to switch the two values to the left and right of the equal sign, etc, but did not work. Someone can help me out?

Solution:

I solved this problem using the codes below:

    statement1='SELECT date FROM table1'
    curs1.execute(statement1)
    records1=curs1.fetchall()
    for record1 in records1:
        date = record1[0].split('/')
        monthDate=date[0]+ '/'+date[1]

        if int(year)%4 == 0: #for leap year
            statement_tmp = "SELECT dayleap FROM table2 WHERE date = '%s'" % (monthDate) #the date column in table2 is in format of month/date.
            curs1.execute(statement_tmp)
            julianDate1 = curs1.fetchall()
            julianDate = curs1.fetchall()[0][0]
            statement = "UPDATE table1 SET juliandate 开发者_JAVA技巧= '%s' WHERE date = '%s'" % (julianDate, fullDate)
            curs1.execute(statement)
            conn.commit()

        else: # for nonleap year

This is a very basic update statement. There should be zero sub-queries and correlated queries:

UPDATE table1 
  SET julian_date = dayleap
FROM table2
WHERE date = %s 

PS:

• I would not name your columns after data types (date). Although its allowed it can bite you in subtle and insidious ways.
• Your hopefully escaping data if comes from a non-trusted source and not just dropping it in.
• This seems like a lot of work for something simple like finding out a leap year. PostgreSQL already knows: select extract(day from (2003 || '-03-01')::date - '1 day'::interval) = 29 where you could paramatize 2003 into any year

You are trying to set julian_date to something that the sub query returns(which is returning more than one result), however you can't assign multiple results. The subquery should only return one result so you can SET julian_date.

Change your subquery to:

SELECT dayleap FROM table2 WHERE date = '%s' LIMIT 1

and you may want to add LIMIT 1 to your 2nd sub query as well.

I solved by splitting the SELECT and UPDATE statements into separate ones. Also, in the statement: SELECT column1 FROM table WHERE column2 = variable.. the column2 is supposed to be within the table, rather than from another table. Otherwise, it cannot find the right records to select or update. There tips are key to solving the problem. See the solution in the question. Thanks