help on SQL UPDATE needed

UPDATE tbl_person SET tbl_person.col_state=2 WHERE
lnk_company_person.col_id_person=tbl_person.col_id_person and
lnk_company_person.col_id_company=3
The error returned by the above UPDATE was:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'lnk_company_person' does not match with a table name or
alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'lnk_company_person' does not match with a table name or
alias name used in the query.
Lnk_company_person is an existing table. Should I use sub-select? How? I
haven't used SQL for some time. Would appreciate any help.
Thanks,
BingYou need to have a FROM clause..
UPDATE tbl_person
SET col_state = 2
FROM tbl_person T, lnk_company_person U
WHERE U.col_id_person = T.col_id_person and
U.col_id_company = 3|||You haven't included the lnk_company_person as a table in the update
statement. The form you need is something like:
UPDATE tbl_person SET col_state=2
WHERE exists (select * from lnk_company_person as lnk
where lnk.col_id_person=tbl_person.col_id_person and
lnk.col_id_company=3)
or
UPDATE tbl_person SET col_state=2
FROM lnk_company_person as lnk
WHERE lnk.col_id_person=tbl_person.col_id_person and
lnk.col_id_company=3
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
> UPDATE tbl_person SET tbl_person.col_state=2 WHERE
> lnk_company_person.col_id_person=tbl_person.col_id_person and
> lnk_company_person.col_id_company=3
> The error returned by the above UPDATE was:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'lnk_company_person' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Line 1
> The column prefix 'lnk_company_person' does not match with a table name or
> alias name used in the query.
> Lnk_company_person is an existing table. Should I use sub-select? How?
I
> haven't used SQL for some time. Would appreciate any help.
> Thanks,
> Bing|||On Tue, 4 Jan 2005 13:57:06 -0800, bing wrote:

>UPDATE tbl_person SET tbl_person.col_state=2 WHERE
>lnk_company_person.col_id_person=tbl_person.col_id_person and
>lnk_company_person.col_id_company=3
>The error returned by the above UPDATE was:
>Server: Msg 107, Level 16, State 3, Line 1
>The column prefix 'lnk_company_person' does not match with a table name or
>alias name used in the query.
>Server: Msg 107, Level 16, State 1, Line 1
>The column prefix 'lnk_company_person' does not match with a table name or
>alias name used in the query.
>Lnk_company_person is an existing table. Should I use sub-select? How? I
>haven't used SQL for some time. Would appreciate any help.
Hi Bing,
It's hard to guess what you want, but try if the following works for you:
UPDATE tbl_person
SET col_state = 2
WHERE EXISTS (SELECT *
FROM lnk_company_person AS l
WHERE l.col_id_person = tbl_person.col_id_person
AND l.col_id_company = 3)
If this doesn't achieve what you want, then you'll need to post more
details, as described here: http://www.aspfaq.com/5006.
Also, please remove the prefixes from your table and column names! Are you
calling yourself person_Bing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks all who replied. I did not know FROM could be used in UPDATE. The
online book does not show FROM in UPDATE's syntax. Anyway, good to learn
something new.
Bing
"Scott Morris" wrote:

> You haven't included the lnk_company_person as a table in the update
> statement. The form you need is something like:
> UPDATE tbl_person SET col_state=2
> WHERE exists (select * from lnk_company_person as lnk
> where lnk.col_id_person=tbl_person.col_id_person and
> lnk.col_id_company=3)
> or
> UPDATE tbl_person SET col_state=2
> FROM lnk_company_person as lnk
> WHERE lnk.col_id_person=tbl_person.col_id_person and
> lnk.col_id_company=3
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
> I
>
>|||>The online book does not show FROM in UPDATE's syntax.
Really? Which topic are you looking at? The UPDATE topic under
Transact-SQL Reference shows the following syntax:
Syntax
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @.variable = expression
| @.variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ]
[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]
If you're not seeing this in the UPDATE topic, please let me know.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"bing" <bing@.discussions.microsoft.com> wrote in message
news:88C20751-F1B5-4194-9958-74ADABC18C91@.microsoft.com...[vbcol=seagreen]
> Thanks all who replied. I did not know FROM could be used in UPDATE. The
> online book does not show FROM in UPDATE's syntax. Anyway, good to learn
> something new.
> Bing
> "Scott Morris" wrote:
>|||Oh, hmmm...The topic I looked at was 'Embedded SQL for C and SQL Server'.
Syntax
UPDATE {table_name | view_name} SET {column=expression[,...]}
WHERE CURRENT
OF cursor_name
Why is this UPDATE syntax different from the one in Transact-SQL Reference?
Bing
"Gail Erickson [MS]" wrote:

> Really? Which topic are you looking at? The UPDATE topic under
> Transact-SQL Reference shows the following syntax:
> Syntax
> UPDATE
> {
> table_name WITH ( < table_hint_limited > [ ...n ] )
> | view_name
> | rowset_function_limited
> }
> SET
> { column_name = { expression | DEFAULT | NULL }
> | @.variable = expression
> | @.variable = column = expression } [ ,...n ]
> { { [ FROM { < table_source > } [ ,...n ] ]
> [ WHERE
> < search_condition > ] }
> |
> [ WHERE CURRENT OF
> { { [ GLOBAL ] cursor_name } | cursor_variable_name
}
> ] }
> [ OPTION ( < query_hint > [ ,...n ] ) ]
> If you're not seeing this in the UPDATE topic, please let me know.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:88C20751-F1B5-4194-9958-74ADABC18C91@.microsoft.com...
>
>|||"Hugo Kornelis" wrote:

> Hi Bing,
> It's hard to guess what you want, but try if the following works for you:
> UPDATE tbl_person
> SET col_state = 2
> WHERE EXISTS (SELECT *
> FROM lnk_company_person AS l
> WHERE l.col_id_person = tbl_person.col_id_person
> AND l.col_id_company = 3)
>
Thanks, Hugo. I believe it should do what I want.

> Also, please remove the prefixes from your table and column names! Are you
> calling yourself person_Bing?
>
Hmm...I don't get this part. Which prefix should not be used? That's how
the tables and columns are defined by the vendor's application. Please
advise.
Bing|||On Wed, 5 Jan 2005 06:39:10 -0800, bing wrote:
(snip)
>Hmm...I don't get this part. Which prefix should not be used? That's how
>the tables and columns are defined by the vendor's application. Please
>advise.
Hi Bing,
If you're stuck with the table and column names as they are, you're stuck
with them. But if you have the power to change them, then do so.
It is generally considered bad practice to prefix table names with tbl_
and column names with col_ (or, another common prefix, some short form of
the data type, like int_). Some googling should bring up many discussions
about this.
The reason why prefixing your table name with tbl_ is bad, is that most
parts of SQL consider tables to be equivalent to views. This means that
you could replace your Customers table with a CustomersNew table, then
create a view named Customers that converts the CustomerNew data to the
"old" format. If you do that, most of your code will simply keep working
without having to be changed.
You can do the same if the table is called tbl_Customers, but then you'll
end up with a *view* called tbl_Customers. This takes the prefix ad
ridiculum. And if you relabel the view as vw_Customers, you'll still have
to go over and change all your current code.
The same goes for column prefixes indicating the datatype (doesn't apply
to your case, but I'm on my soapbox now <g> ). I've seen numerous examples
of columns named int_Size or float_Amount. What will happen if some change
in the business requires you to make Size a bigint? Or a decimal (15,2)?
Will you go over all your code, changing each occurence of int_Size to
dec_Size or bigint_Size? Will you use global search and replace (with the
risk of an unwanted change in the wrong place)? Who'll retest all the code
affected by this simple change?
As to the prefix col_, the risk of change is not there - but it just seems
silly to type col_ in front of each column name. Just as it would be silly
if we all addressed all other people with "Person (name)" (e.g. "Person
Bing", "Person Hugo", "Person Scott" and "Person Gail").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo, good to know. Thanks much for your lucid explanation. Right now, I'
m
unfortunately pretty much stuck with the vendor.
Bing
"Hugo Kornelis" wrote:

> On Wed, 5 Jan 2005 06:39:10 -0800, bing wrote:
> (snip)
> Hi Bing,
> If you're stuck with the table and column names as they are, you're stuck
> with them. But if you have the power to change them, then do so.
> It is generally considered bad practice to prefix table names with tbl_
> and column names with col_ (or, another common prefix, some short form of
> the data type, like int_). Some googling should bring up many discussions
> about this.
> The reason why prefixing your table name with tbl_ is bad, is that most
> parts of SQL consider tables to be equivalent to views. This means that
> you could replace your Customers table with a CustomersNew table, then
> create a view named Customers that converts the CustomerNew data to the
> "old" format. If you do that, most of your code will simply keep working
> without having to be changed.
> You can do the same if the table is called tbl_Customers, but then you'll
> end up with a *view* called tbl_Customers. This takes the prefix ad
> ridiculum. And if you relabel the view as vw_Customers, you'll still have
> to go over and change all your current code.
> The same goes for column prefixes indicating the datatype (doesn't apply
> to your case, but I'm on my soapbox now <g> ). I've seen numerous examples
> of columns named int_Size or float_Amount. What will happen if some change
> in the business requires you to make Size a bigint? Or a decimal (15,2)?
> Will you go over all your code, changing each occurence of int_Size to
> dec_Size or bigint_Size? Will you use global search and replace (with the
> risk of an unwanted change in the wrong place)? Who'll retest all the code
> affected by this simple change?
> As to the prefix col_, the risk of change is not there - but it just seems
> silly to type col_ in front of each column name. Just as it would be silly
> if we all addressed all other people with "Person (name)" (e.g. "Person
> Bing", "Person Hugo", "Person Scott" and "Person Gail").
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>