SELECT inside an UPDATE query

Tags: ,



I’m trying to select a inner table while running an update, but I keep receiving an syntax error can anyone see what I’m doing wrong Thanks.

Syntax error: Encountered "SELECT" at line 1, column 94.

String sql =  "UPDATE MEMBER SET FIRSTNAME=?, LASTNAME=?, STREETADDRESS=?, CITY=?, STATE=?, ZIP=?, PHONE=?, SELECT MEMBERSHIPID WHERE MEMNAME=? WHERE MEMBERID=?";
                                PreparedStatement stmt = db.getPreparedStatement(sql);
                                stmt.setString(1, fName);
                                stmt.setString(2, lName);
                                stmt.setString(3, streetA);
                                stmt.setString(4, city);
                                stmt.setString(5, state);
                                stmt.setString(6, zc);
                                stmt.setString(7, phon);
                                stmt.setString(8, memSelection); 
                                stmt.setInt(9, Integer.parseInt(memberID)); 

enter image description here

Answer

You need to write your SELECT as an assignment from a subquery (enclosed in parentheses), and also specify the table you are selecting the value from:

UPDATE MEMBER 
SET FIRSTNAME=?, 
    LASTNAME=?, 
    STREETADDRESS=?, 
    CITY=?, 
    STATE=?, 
    ZIP=?,
    PHONE=?,
    MEMBERSHIPID = (SELECT MEMBERSHIPID 
                    FROM MEMBERSHIP 
                    WHERE MEMNAME=?)
WHERE MEMBERID=?


Source: stackoverflow