update with select

cydeways06

i was wondering if something like this is possible. I keep getting an error message on the select state even though a user comment in the mysql documentation says it is possible. The query lookes like this

UPDATE tmp_report_info
SET grounddate_del ='(SELECT pac_date
FROM tmp_actual_materials
WHERE lotid=".$row['lotid']." AND main_phase = 'Ground')' WHERE lotid = ".$row['lotid'][/code]

Tried to format a little don't know if its going to help or makes this worse. Any thoughts would help. Thanks

Mike

stolzyboy

what is the error

ultraslacker

Most likely the version of mysql you are running does not support this.

cydeways06

it is telling me that there is an syntax error starting at SELECT

and i am running mysql 4.0 so i would think that it would be supported

mike

stolzyboy

looks to me like it isn't supported, you could just run the select query first, then put that into the update query and then run that

not the most elegant, but you gotta work with what ya got

cydeways06

Where did you find the information about it not being supported....i kinda figured i was going to have to resort to that method. THanks for the hlep

stolzyboy

from mysql.com

Subqueries are supported in MySQL version 4.1