SQL - UPDATE statement help....

[Guest]

update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg

Can anyone tell me why I get a parameter pop window when I execute this?

Does SQL Jet UPDATE work the same as T-SQL UPDATE?

This is driving me crazy... I've looked up every instance of help for the
UPDATE statement and as to what the parameters are for it.

Thanks for your help!!

 

[Guest]

nevermind.... I got it to work.. however not using the original statement....

Why did this work, with no pop up windows requesting parameters....

update mailstops right join tblbldg
on mailstops.bldg_app = tblbldg.bldg
set mailstops.bldgid = tblbldg.bldgid

and this didn't??
update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg

 

[John Vinson]

nevermind.... I got it to work.. however not using the original statement....

Why did this work, with no pop up windows requesting parameters....

update mailstops right join tblbldg
on mailstops.bldg_app = tblbldg.bldg
set mailstops.bldgid = tblbldg.bldgid

and this didn't??
update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg

It didn't work because you're referring to tblbldg - which is defined
only INSIDE the subquery - outside the parentheses. This should work:

update mailstops
set blgdid = (select bldgid from tblbldg
where mailstops.bldg_abb = tblbldg.bldg)

but the Right Join will be faster and more efficient.

John W. Vinson[MVP]

 

[Guest]

'tblbldg.bldg' cannot be resolved as it is outsiode the parentheses and
'mailstops.bldg_abb ' cannot be resolved since there is no FROM clause for
the mailstops table.

update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg

 

[Guest]

Thanks for the reply.

Though one last follow up..

update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg

This doesn't work because no table is defined correct? ie. no FROM clause.

So if I did this, is it supposed work?? I actually tried a bunch of
different statements to get this table updated, including the FROM clause and
would always get an error when I used it. I even tried the statement below
but nothing.

update mailstops
set blgdid = (select bldgid from tblbldg)
from mailstops, tblbldg
where mailstops.bldg_abb = tblbldg.bldg

 

[Neil Sunderland]

So if I did this, is it supposed work?? I actually tried a bunch of
different statements to get this table updated, including the FROM clause and
would always get an error when I used it. I even tried the statement below
but nothing.

update mailstops
set blgdid = (select bldgid from tblbldg)
from mailstops, tblbldg
where mailstops.bldg_abb = tblbldg.bldg

Try this:
UPDATE mailstops
SET blgdid =
(SELECT MIN(bldgid)
FROM mailstops, tblbldg
WHERE mailstops.bldg_abb = tblbldg.bldg)

 

[Neil Sunderland]

Try this:
UPDATE mailstops
SET blgdid =
(SELECT MIN(bldgid)
FROM mailstops, tblbldg
WHERE mailstops.bldg_abb = tblbldg.bldg)

No, don't try that - it won't work...

Try this instead:

UPDATE mailstops
SET blgdid =
(SELECT MIN(bldgid)
FROM tblbldg
WHERE mailstops.bldg_abb = tblbldg.bldg)

 

[Guest]

THANKS FOR YOUR HELP!!!

No, don't try that - it won't work...

Try this instead:

UPDATE mailstops
SET blgdid =
(SELECT MIN(bldgid)
FROM tblbldg
WHERE mailstops.bldg_abb = tblbldg.bldg)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address