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(at)yahoo(dot)com>
<operationsengineer1(at)yahoo(dot)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

On 12/13/05, operationsengineer1(at)yahoo(dot)com
<operationsengineer1(at)yahoo(dot)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 ;)

operationsengineer1(at)yahoo(dot)com <operationsengineer1(at)yahoo(dot)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 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

--- Charley Tiggs <ctiggs(at)xpressdocs(dot)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

operationsengineer1(at)yahoo(dot)com <operationsengineer1(at)yahoo(dot)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°