Thread: Update Query Problem
the following update query... UPDATE t_job_number SET contract_id = 30 WHERE t_serial_number.serial_number_id = 78 AND t_serial_number.job_number_id = t_job_number.job_number_id yields the following chastisement... ERROR: missing FROM-clause entry for table "t_serial_number" can anyone point in the right direction? requiring a from clause in this update seems a little bizarre to me - so i'm sure i've botched something up pretty good. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Dec 13, 2005, at 11:30 AM, <operationsengineer1@yahoo.com> <operationsengineer1@yahoo.com> wrote: > the following update query... > > UPDATE t_job_number > SET contract_id = 30 > WHERE t_serial_number.serial_number_id = 78 > AND t_serial_number.job_number_id = > t_job_number.job_number_id > > yields the following chastisement... > > ERROR: missing FROM-clause entry for table > "t_serial_number" > > can anyone point in the right direction? requiring a > from clause in this update seems a little bizarre to > me - so i'm sure i've botched something up pretty > good. > > tia... There needs to be a reference to t_serial_number in the update clause: UPDATE t_job_number, t_serial_number SET contract_id = 30 WHERE t_serial_number.serial_number_id = 78 AND t_serial_number.job_number_id = t_job_number.job_number_id Charley
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> schrieb: > the following update query... > > UPDATE t_job_number > SET contract_id = 30 > WHERE t_serial_number.serial_number_id = 78 > AND t_serial_number.job_number_id = > t_job_number.job_number_id Try this: UPDATE t_job_number SET contract_id = 30 WHERE job_number_id = (select job_number_id from t_serial_number where serial_number_id = 78); I'm not sure if i understand you corrently... HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 12/13/05, operationsengineer1@yahoo.com
<operationsengineer1@yahoo.com> wrote:
> the following update query...
>
> UPDATE t_job_number
> SET contract_id = 30
> WHERE t_serial_number.serial_number_id = 78
> AND t_serial_number.job_number_id =
> t_job_number.job_number_id
>
> yields the following chastisement...
>
> ERROR: missing FROM-clause entry for table
> "t_serial_number"
>
> can anyone point in the right direction? requiring a
> from clause in this update seems a little bizarre to
> me - so i'm sure i've botched something up pretty
> good.
>
> tia...
>
UPDATE t_job_number SET contract_id = 30
WHERE job_number_id IN (SELECT job_number_id
FROM t_serial_number
WHERE serial_number_id = 78);
or add a FROM clause to the update
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On Tue, Dec 13, 2005 at 11:37:00AM -0600, Charley Tiggs wrote:
> There needs to be a reference to t_serial_number in the update clause:
>
> UPDATE t_job_number, t_serial_number
> SET contract_id = 30
> WHERE t_serial_number.serial_number_id = 78
> AND t_serial_number.job_number_id =
> t_job_number.job_number_id
No version of PostgreSQL that I tested (7.3 and later) allows that
syntax, and it's not shown in the documentation.
ERROR: syntax error at or near "," at character 20
LINE 1: UPDATE t_job_number, t_serial_number
^
--
Michael Fuhr
Ooops. apologies. I've been in mysql too long. Charley On Dec 13, 2005, at 11:49 AM, Michael Fuhr wrote: > On Tue, Dec 13, 2005 at 11:37:00AM -0600, Charley Tiggs wrote: >> There needs to be a reference to t_serial_number in the update >> clause: >> >> UPDATE t_job_number, t_serial_number >> SET contract_id = 30 >> WHERE t_serial_number.serial_number_id = 78 >> AND t_serial_number.job_number_id = >> t_job_number.job_number_id > > No version of PostgreSQL that I tested (7.3 and later) allows that > syntax, and it's not shown in the documentation. > > ERROR: syntax error at or near "," at character 20 > LINE 1: UPDATE t_job_number, t_serial_number > ^ > > -- > Michael Fuhr
man, you guys are ON IT! thanks. the following worked like a charm: UPDATE t_job_number SET contract_id = 30 WHERE job_number_id = (select job_number_id from t_serial_number where serial_number_id = 78); i have to spend some time figuring out exactly where i fumbled the ball the first time around... thanks, everyone. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> schrieb: > man, you guys are ON IT! thanks. the following > worked like a charm: > > UPDATE t_job_number > SET contract_id = 30 > WHERE job_number_id = (select job_number_id from > t_serial_number where serial_number_id = 78); Please read also the mail from 'Jaime Casanova', if the subselect returns multiple rows. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--- Charley Tiggs <ctiggs@xpressdocs.com> wrote: > Ooops. apologies. I've been in mysql too long. > > Charley > > On Dec 13, 2005, at 11:49 AM, Michael Fuhr wrote: > > > On Tue, Dec 13, 2005 at 11:37:00AM -0600, Charley > Tiggs wrote: > >> There needs to be a reference to t_serial_number > in the update > >> clause: > >> > >> UPDATE t_job_number, t_serial_number > >> SET contract_id = 30 > >> WHERE t_serial_number.serial_number_id = 78 > >> AND t_serial_number.job_number_id = > >> t_job_number.job_number_id > > > > No version of PostgreSQL that I tested (7.3 and > later) allows that > > syntax, and it's not shown in the documentation. > > > > ERROR: syntax error at or near "," at character > 20 > > LINE 1: UPDATE t_job_number, t_serial_number > > ^ > > > > -- > > Michael Fuhr actually, i've never used mysql (although that excuse might be nice right now!). this was my first update... and it didn't go very well. -lol- as for "line 1", i knew that didn't work b/c i tried it in pgadmin - mostly out of desparation. i guess i inadvertantly copied it to my note. make no mistake, the query didn't work even w/o that bad synta.! hopefully i'll be able to build off the basic update example provided earlier so i can avoid trouble going forward. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com