update another table
-
Hi all,
I have a update statement which will do the update 3 columns from test table to store_table table.
However I got error message "Invalid object Store_Table" and I am positive that I didn't misspell the table name.
here is my code:
UPDATE Store_Table
SET Broadband = t .broadband
FROM test t, store_table s
WHERE (s.Store_Num = t .store_num)
what's wrong with my code?
Thank you.
-
When you alias a table you need to use the alias instead of the table name.
Change "UPDATE Store_Table" to "UPDATE s"
I would also suggest you get into the habit of having more descriptive aliases. Helps immensely when you look at the code 6 months later
--------------------
Colt 45 - the original point and click interface
-
We've had a very similar post here recently (about aliases when updating), but I'm not so sure this is the reason. You should use aliases once you start with it, but sometimes (especially in updates) SQL Server assumes what table you mean if you forget. In fact, the SQL as posted does not throw any errors in my test DB. Also, the error when you forget to use alias, is much more descriptive... it would read like this:
The column prefix 'Store_Table' does not match with a table name or alias name used in the query.
"Invalid object Store_Table" means you really don't have any table of that name in that database. If you are positive that you did not misspell the table name, check whether you are in the correct database, and also check table ownership.
What does this return in the same database, if anything?
SELECT usr.[name] as owner, obj.[name] as table_name
FROM sysobjects obj
JOIN sysusers usr ON obj.uid = usr.uid
WHERE obj.[name] LIKE '%store_table%'
-
The examples in BOL of using the FROM clause in an update seem to be inconsistent. It doesn't seem to me to be clear whether you have to specify the table you are updating in both the UPDATE and the FROM clause. Maybe it is optional to reference it additionally in the FROM clause
Have you tried removing Store_Table from the FROM clause...
UPDATE Store_Table
SET Broadband = t .broadband
FROM test t
WHERE (Store_Table.Store_Num = t .store_num)
David
If it ain't broke, don't fix it...
-
Jennifer,
Contrary to what many have posted above, there is absolutely nothing wrong, not even the thing about the aliases, with your code. It is very likely a simple matter of permissions or "user name" confusion. If you created the table under your login and you aren't a member of the "DBO" role, then the table name was created under your user name and you need to use the 2 part naming convention to see the table.
For example, if your user login name on the server is "jenniferhu", then you may need to do something like the following for selects...
SELECT *
FROM jenniferhu.Store_Table
If, in your table creation statement, you said to CREATE TABLE dbo.Store_Table, then "dbo" becomes the owner and no one else can use it unless you do a "grant".
GRANT ALL ON dbo.Store_Table TO PUBLIC
You can limit the grant by changing ALL to just SELECT.
Even if you have DBO privs, I'd recommend that you make sure your table creations and other SQL code use the two part naming convention. I makes for faster code and keeps folks from stepping on each other if they make the mistake of naming tables identically.
To find the owner of a table for the 2 part naming convention, try this...
SELECT Name, USER_NAME(uid)
FROM dbo.SysObjects (NOLOCK)
WHERE Name = 'Store_Table'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply