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.

[Brian]

<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.

[Hugo Kornelis]

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)

[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

[Joe Celko]

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!