How to Update Multiple table records using table alias???

Posted by prakashmondal5-21103 under Sql Server on 4/6/2013 | Points: 10 | Views : 2600 | Status : [Member] | Replies : 4
I create two table..

create table stupersonal (stuid varchar(20) primary key,stuaddress varchar(20),stufname varchar(20));

create table stuclass (stuid varchar(20) primary key,stuclass varchar(20),stusec varchar(20));



also insert some values...............

but, I update some value to both table.

I write...................using table alias in SQL SERVER-2005

update stupersonal sp, stuclass sc set sp.stuaddress='Kolkata',sp.stufname='Smith',sc.stuclass='X', sc.stusec='A' where sp.stuid='amit123' and sc.stuid='amit123';


but. error.......................
can't update this value to those tables.................

Please solve this problem..............

.............................Prakash




Responses

Posted by: Jayakumars on: 4/7/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

try this

update stupersonal1 set stuaddress='Kolkata',stufname='Smith' where stuid='amit123'
Update stuclass1 set stuclss='X',stusec='A',stuid='amit123'

or using Trigger like this

update stupersonal1 set stuaddress='Kolkata',stufname='Smith' where stuid='amit123'
CREATE Trigger Testt1 on stupersonal
After Update
as
Update stuclass1 set stuclss='X',stusec='A',stuid='amit123'


Mark as Answer if its helpful to you

prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Brahmam on: 4/7/2013 [Member] Starter | Points: 25

Up
0
Down
try this

UPDATE stupersonal , stuclass
SET stupersonal .stuaddress = ' XXXXXX' ,stupersonal .stufname ="xxx",
,stuclass .stuclass= 'start,stop',stuclass.stusec ="xxx"
FROM stupersonal T1, stuclass T2
WHERE T1.stuid = T2.stuid
and T1.stuid = "xxxx"
this working

brahmam

prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: manoranjanguptaoutlook-19221 on: 4/8/2013 [Member] Starter | Points: 25

Up
0
Down
Please go step by step :

1>create table tblmanuemployee(ID int, name varchar(10), salary int )

2>create table job(ID int, title varchar(10), averageSalary int)

insert data in both table using simple query

>UPDATE tblmanuemployee SET Salary = 4000 FROM Employee INNER JOIN Job ON Employee.ID = Job.ID

it will work fine
after that fire select query

Manoranjan Gupta Manu
9873306227

prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/27/2013 [Member] Starter | Points: 25

Up
0
Down
HEY Brahmam ,

Are you using Sql serer if yes please let me know the version?



Visit my blog www.tutorialsqlserver.com

prakashmondal5-21103, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response