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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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°

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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°

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--- 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly