SQL - UPDATE statement help....

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!
 
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
 
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
Click to expand...

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]
 
'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
 
Thanks for the reply.

Though one last follow up..
update mailstops
set blgdid = (select bldgid from tblbldg)
where mailstops.bldg_abb = tblbldg.bldg
Click to expand...

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
 
awrex said:
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
Click to expand...

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

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)
 
THANKS FOR YOUR HELP!!!

Neil Sunderland said:
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
Click to expand...
 
Back
Top