updation issue - 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

Transact-SQL (2000)

updation issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-27 : 16:05:26

This updation doesn't seems to work.its giving some error as Cannot use the column prefix 'E'.

UPDATE tbl_emp_master
SET E.ref_id=S.ref_id,E.order_number=S.order_id
FROM tbl_emp_master E INNER JOIN tbl_service_details S
ON E.service_id=S.service_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 16:12:10

UPDATE E
SET ref_id = S.ref_id, order_number = S.order_id
FROM tbl_emp_master E
INNER JOIN tbl_service_details S
ON E.service_id = S.service_id

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-27 : 16:22:45

Works fine ,Thanks tduggan

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-27 : 20:56:13

can I update 2 tables in that query say I want to update a field called 'status' in tbl_service_details

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-27 : 21:28:55

This statement when trying to update both the tables doesn't work.
UPDATE E ,S
SET ref_id = S.ref_id, order_number = S.order_id ,S.status='Y'
FROM tbl_emp_master E
INNER JOIN tbl_service_details S
ON E.service_id = S.service_id

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 17:10:08

You will have to break it up into 2 updates.
UPDATE can only work on 1 table at a time.

BEGIN TRANSACTION upd

UPDATE E
SET E.ref_id = S.ref_id, E.order_number = S.order_id
FROM tbl_emp_master E
INNER JOIN tbl_service_details S
ON E.service_id = S.service_id

UPDATE S
SET S.status='Y'
FROM tbl_emp_master E
INNER JOIN tbl_service_details S
ON E.service_id = S.service_id

COMMIT TRANSACTION upd

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-29 : 18:45:50

quote:
Originally posted by sqllearner

This updation doesn't seems to work.its giving some error as Cannot use the column prefix 'E'.

UPDATE tbl_emp_master
SET E.ref_id=S.ref_id,E.order_number=S.order_id
FROM tbl_emp_master E INNER JOIN tbl_service_details S
ON E.service_id=S.service_id

Tara what is wrong with above code.
seems allright.
Difference between your code and this code is aliases.

right?
Thanks

mk_garg

a_shipra
Starting Member

20 Posts

Posted - 2004-08-29 : 20:34:49

I think in this code we have specified the full table name in the first place so it will not accept the alias and is looking for the full table name.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-29 : 20:57:52

You may be right.
Any other thought on this.

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources