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...
> >
> > 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
>
>|||>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...
> 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...
>> >
>> > 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
>>|||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:
> >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...
> > 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...
> >> >
> >> > 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
> >>
> >>
> >>
>
>|||"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)
>> 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.
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)
> >> 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.
> 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)
>|||I disagree the argument by Hugo!.. Sorry, but its always good practice
to prefix tables, stored proces, views, triggers, indexes with
appropriate prefixes (and sometime with suffix as well)... I agree its
not a good practice to prefix the column names with the data type.. But
most of the time, these tables, stored procs, views and triggers are
never going to be replaced by anything else. Its always easy to read
and also following naming standards will make any developer understand
the objects more quickly...! There are a ton of threads on this topic
on google and other newsgroups...|||> Oh, hmmm...The topic I looked at was 'Embedded SQL for C and SQL Server'.
> Why is this UPDATE syntax different from the one in Transact-SQL
> Reference?
My guess is that the topic was not updated. The whole Embedded SQL for C
API was deprecated quite some time ago. (It's not supported at all in SQL
Server 2005). As such, I doubt that the writers spent any significant time
in those topics.
At any rate, I assume you found that topic in error and you're not trying
to use the Embedded SQL for C funcationality. I know that finding the
correct topics in BOL can be a bit of a challenge (we're working on making
it easier). It's always good to check the blue bar at the top of the topic
to make sure you're in the right area. For T-SQL statements, you really
want to be in the Transact-SQL Reference area.
Hope that helps,
--
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:8111C214-8648-425C-9ECF-D3BFE4DF7C86@.microsoft.com...
> 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:
>> >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...
>> > 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...
>> >> >
>> >> > 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 5 Jan 2005 10:21:04 -0800, SQLDBA wrote:
>I disagree the argument by Hugo!.. Sorry, but its always good practice
>to prefix tables, stored proces, views, triggers, indexes with
>appropriate prefixes (and sometime with suffix as well)... I agree its
>not a good practice to prefix the column names with the data type.. But
>most of the time, these tables, stored procs, views and triggers are
>never going to be replaced by anything else. Its always easy to read
>and also following naming standards will make any developer understand
>the objects more quickly...! There are a ton of threads on this topic
>on google and other newsgroups...
Hi SQLDBA,
You might wish to consult ISO-11179, the international standard for naming
conventions.
Unfortunately, this standard is not available for free. Joe Celko often
posts a good summary of the most important aspects. Google for "Celko" +
"ISO-11179" and you should be able to find it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)