471,077 Members | 810 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,077 software developers and data experts.

Update sql syntex question

rc
Hi

I am kind of new to sql, so here goes my question

I have this sql update query

update [CACI-table] set premeses=[xaa].[Sub Building]
where (premeses is null)

Waht is wrong with this.

I have tow table called xaa and CACI-table and all I want to do is
take one column from one table and and put itto the other

Query analysider says the following err message

The column prefix 'xaa' does not match with a table name or alias name
used in the query.
Jul 20 '05 #1
4 2002
<rc@die@you@!spammers.sandworm.demon.co.uk> wrote in message
news:nl********************************@4ax.com...
Hi

I am kind of new to sql, so here goes my question

I have this sql update query

update [CACI-table] set premeses=[xaa].[Sub Building]
where (premeses is null)

Waht is wrong with this.

I have tow table called xaa and CACI-table and all I want to do is
take one column from one table and and put itto the other

Query analysider says the following err message

The column prefix 'xaa' does not match with a table name or alias name
used in the query.


You need to use a FROM clause. I can't give you the exact query because you
haven't provided any information on how the tables are related to each
other. There are several examples in Books Online.

Jul 20 '05 #2
On Sat, 02 Oct 2004 13:20:47 +0100,
rc@die@you@!spammers.sandworm.demon.co.uk wrote:
Hi

I am kind of new to sql, so here goes my question

I have this sql update query

update [CACI-table] set premeses=[xaa].[Sub Building]
where (premeses is null)

Waht is wrong with this.

I have tow table called xaa and CACI-table and all I want to do is
take one column from one table and and put itto the other

Query analysider says the following err message

The column prefix 'xaa' does not match with a table name or alias name
used in the query.


Hi rc,

You have to tell SQL Server from what row in the xaa table it should take
the Sub Building. The easiest way to do this, in your situation, is by
using a subquery. Something like this:

UPDATE [CACI-table]
SET premeses = (SELECT [Sub Building]
FROM [xaa]
WHERE [xaa].[Some column] = [CACI-table].[Some column]
AND ....
)

The WHERE clause of the subquery can be simple or complex. It probably
should refer in some way to columns in the table to be updated (assuming
that the "correct" row in xaa is different for each row in CACI-table) and
it should return exactly one row for each row in CACI-table. You'll get an
error if it returns more than one row; if it returns no rows, the value of
premeses in the corresponding CACI-table row will be set to NULL.

If you have trouble getting this right, post again but provide a little
more detail, like:
* Structure of your tables (as CREATE TABLE statements, including all
constraints - see http://www.aspfaq.com/etiquette.asp?id=5006).
* Sample data that shows what you want to achieve (as INSERT statements,
see http://vyaskn.tripod.com/code.htm#inserts).
* Expected output for the sample data provided.
* A concise description of the business problem you want to solve.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3
rc
Thanks, I understand now

Thanks again
Hi

I am kind of new to sql, so here goes my question

I have this sql update query

update [CACI-table] set premeses=[xaa].[Sub Building]
where (premeses is null)

Waht is wrong with this.

I have tow table called xaa and CACI-table and all I want to do is
take one column from one table and and put itto the other

Query analysider says the following err message

The column prefix 'xaa' does not match with a table name or alias name
used in the query.


Hi rc,

You have to tell SQL Server from what row in the xaa table it should take
the Sub Building. The easiest way to do this, in your situation, is by
using a subquery. Something like this:

UPDATE [CACI-table]
SET premeses = (SELECT [Sub Building]
FROM [xaa]
WHERE [xaa].[Some column] = [CACI-table].[Some column]
AND ....
)

The WHERE clause of the subquery can be simple or complex. It probably
should refer in some way to columns in the table to be updated (assuming
that the "correct" row in xaa is different for each row in CACI-table) and
it should return exactly one row for each row in CACI-table. You'll get an
error if it returns more than one row; if it returns no rows, the value of
premeses in the corresponding CACI-table row will be set to NULL.

If you have trouble getting this right, post again but provide a little
more detail, like:
* Structure of your tables (as CREATE TABLE statements, including all
constraints - see http://www.aspfaq.com/etiquette.asp?id=5006).
* Sample data that shows what you want to achieve (as INSERT statements,
see http://vyaskn.tripod.com/code.htm#inserts).
* Expected output for the sample data provided.
* A concise description of the business problem you want to solve.

Best, Hugo


Jul 20 '05 #4
Search for a copy of one of my standard "cut & paste" descriptions of
how an UPDATE works. If I post it again, the regulars will scream.
Adam also has an old posting showing why you never, never use
proprietary UPDATE.. FROM .. syntax.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by rc | last post: by
16 posts views Thread by robert | last post: by
1 post views Thread by Nikhil Patel | last post: by
5 posts views Thread by Leanne | last post: by
12 posts views Thread by si_owen | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.