A simple one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A simple one

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2006-08-10 : 07:40:03

My brain doesn't seem to be in gear today :(

I have the following update statement that updates from one table to another based on the bit field [toaction] being true

update tblGuest
set g.guest_mailflag = gg.guest_mailflag,
g.guest_title = gg.guest_title,
g.guest_forename = gg.guest_forename,
g.guest_surname = gg.guest_surname,
g.guest_company = gg.guest_company,
g.guest_address1 = gg.guest_address1,
g.guest_address2 = gg.guest_address2,
g.guest_city = gg.guest_city,
g.guest_postcode = gg.guest_postcode,
g.guest_country = gg.guest_country,
g.guest_telephone = gg.guest_telephone,
g.guest_email = gg.guest_email,
g.guest_profiletype = gg.guest_profiletype,
g.guest_createddate = gg.guest_createddate,
g.guest_lastchangedate = gg.guest_lastchangedate,
g.guest_smaccountID = gg.guest_smaccountID,
g.guest_noshowcount = gg.guest_noshowcount,
g.guest_nextarrivaldate = gg.guest_nextarrivaldate,
g.guest_savehistory = gg.guest_savehistory,
g.guest_CXcount = gg.guest_CXcount,
g.guest_joborcoaddress = gg.guest_joborcoaddress,
g.guest_birthdate = gg.guest_birthdate,
g.guest_crcard = gg.guest_crcard
from tblGuest g
join tblGuest0506 gg on gg.guest_propertyfidelio = g.guest_propertyfidelio
where gg.toaction = 1

yet I get errors and I can't fathom why.

Server: Msg 1032, Level 15, State 1, Line 13
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 14
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 15
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 16
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 17
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 18
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 19
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 20
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 21
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 22
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 23
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 24
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 25
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 26
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 27
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 28
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 29
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 30
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 31
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 32
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 33
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 34
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.
Server: Msg 1032, Level 15, State 1, Line 35
Cannot use the column prefix 'g'. This must match the object in the UPDATE clause 'tblGuest'.

Your help would be much appreciated

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 07:58:01

update g
set g.guest_mailflag = gg.guest_mailflag
...

alanlambert
Starting Member

26 Posts

Posted - 2006-08-10 : 07:59:13

If you don't use the alias g it should work fine.

update tblGuest
set tblGuest.guest_mailflag = gg.guest_mailflag,
tblGuest.guest_title = gg.guest_title,
tblGuest.guest_forename = gg.guest_forename,
tblGuest.guest_surname = gg.guest_surname,
tblGuest.guest_company = gg.guest_company,
tblGuest.guest_address1 = gg.guest_address1,
tblGuest.guest_address2 = gg.guest_address2,
tblGuest.guest_city = gg.guest_city,
tblGuest.guest_postcode = gg.guest_postcode,
tblGuest.guest_country = gg.guest_country,
tblGuest.guest_telephone = gg.guest_telephone,
tblGuest.guest_email = gg.guest_email,
tblGuest.guest_profiletype = gg.guest_profiletype,
tblGuest.guest_createddate = gg.guest_createddate,
tblGuest.guest_lastchangedate = gg.guest_lastchangedate,
tblGuest.guest_smaccountID = gg.guest_smaccountID,
tblGuest.guest_noshowcount = gg.guest_noshowcount,
tblGuest.guest_nextarrivaldate = gg.guest_nextarrivaldate,
tblGuest.guest_savehistory = gg.guest_savehistory,
tblGuest.guest_CXcount = gg.guest_CXcount,
tblGuest.guest_joborcoaddress = gg.guest_joborcoaddress,
tblGuest.guest_birthdate = gg.guest_birthdate,
tblGuest.guest_crcard = gg.guest_crcard
from tblGuest
join tblGuest0506 gg on gg.guest_propertyfidelio = tblGuest.guest_propertyfidelio
where gg.toaction = 1

Alan

jparker
Posting Yak Master

118 Posts

Posted - 2006-08-10 : 08:15:38

thanks very much that worked

I have used aliases before on joined updates do you happen to know why it didn't work on this ocassion?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 08:25:33

I think the answer is :

In the statement
update tblGuest set g.guest_mailflag = ....
the g. is not of tblGuest
(u r going to update tblGuest, but columns of an alias g)

The other 2 works

update g set g.guest_mailflag = ....
because u updating fields of g (aliased later)

update tblGuest set tblGuest.guest_mailflag = ....
because u updating fields of tblGuest

The "From" part is mainly to select the records, but not to select the fields (and belonging table)

Srinika

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-10 : 08:31:37

The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 09:08:39

quote:
Originally posted by mahesh_bote

The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL

Mahesh

Which version of SQL Server you are talking about?..it works fine in SQL 2000. See this....

create table SomeTable
(
	a int
)

insert into SomeTable values(1)

update x
set x.a = 2
from SomeTable as x

Harsh Athalye
India.
"Nothing is Impossible"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-10 : 09:23:48

my mistake,

thanx harsh. i havn't read the que carefully.

Mahesh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-10 : 10:44:11

quote:

create table SomeTable
(
	a int
)

insert into SomeTable values(1)

update x
set x.a = 2
from SomeTable as x

In your example, you are aliasing the FROM table to the same name as the UPDATE table. In your SET statement, you are using the fully qualified table name, not the alias.
Try his code in SQL2000 and it will fail. You would have to do it like Alan suggested.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 11:07:39

quote:
Originally posted by DonAtWork

quote:

create table SomeTable
(
a int
)

insert into SomeTable values(1)

update x
set x.a = 2
from SomeTable as x

In your example, you are aliasing the FROM table to the same name as the UPDATE table. In your SET statement, you are using the fully qualified table name, not the alias.
Try his code in SQL2000 and it will fail. You would have to do it like Alan suggested.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

If you want to use alias, you should use it consistently everywhere.

If you write like

Update SomeTable
set x.col = 'something'
from SomeTable as x

it will definitely fail, since you are not consistent in your use of alias.

"In your SET statement, you are using the fully qualified table name, not the alias"

I don't know what do you mean by this...I am using alias only in the set statement not the original table name...Can you explain more?

Harsh Athalye
India.
"Nothing is Impossible"

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 11:08:32

I am using the following SP and it works great, including the alias:

/*toekennen nieuwe locatie*/
UPDATE loc
SET loc.WMSPICKINGLOCATION= itemNewLoc.locationNew
FROM Ax30adnTst.dbo.INVENTITEMLOCATION loc INNER JOIN
(select invent.ITEMID as itemid,
(SELECT TOP 1 WMSLOCATION.wmslocationid
FROM WMSLOCATION
WHERE WMSLOCATION.SBA_PRINTGROUP = '' AND
WMSLOCATION.wmslocationid Not Like 'a%'
ORDER BY newid()) AS locationNew
FROM Ax30adnTst.dbo.INVENTTABLE invent INNER JOIN Ax30adnTst.dbo.purchline pl ON
invent.itemid = pl.itemid
where pl.purchid like '%I100161291') as itemNewLoc ON
loc.ITEMID = itemNewLoc.ITEMID
where loc.inventdimid not like '%Axapta'

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-10 : 12:12:24

Ignore me, working on too many things at one time

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources