If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL - UPDATE statement help....
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!! |
#2
|
|||
|
|||
SQL - UPDATE statement 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 "awrex" wrote: 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!! |
#3
|
|||
|
|||
SQL - UPDATE statement help....
On Mon, 2 Oct 2006 16:01:02 -0700, awrex
wrote: 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] |
#4
|
|||
|
|||
SQL - UPDATE statement help....
'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 "awrex" wrote: 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 "awrex" wrote: 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!! |
#5
|
|||
|
|||
SQL - UPDATE statement help....
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 "awrex" wrote: 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 "awrex" wrote: 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!! |
#6
|
|||
|
|||
SQL - UPDATE statement help....
awrex wrote:
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 Braunton, Devon Please observe the Reply-To address |
#7
|
|||
|
|||
SQL - UPDATE statement help....
Neil Sunderland wrote:
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) -- Neil Sunderland Braunton, Devon Please observe the Reply-To address |
#8
|
|||
|
|||
SQL - UPDATE statement help....
THANKS FOR YOUR HELP!!!
"Neil Sunderland" wrote: Neil Sunderland wrote: 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) -- Neil Sunderland Braunton, Devon Please observe the Reply-To address |
Thread Tools | |
Display Modes | |
|
|