Trying to update 3 tables with 1 query [in vain]

Question

When i want to populate my grid I use this Select command :

SELECT t1.Id, t1.emertimi, kredite, pershkrim, Viti, t2.Emertimi as Expr1, emri, mbiemri
FROM Kurse as t1 INNER JOIN 
     Deget as t2 ON t1.degID = t2.Id INNER JOIN
     Pedgoge ON t1.Id = Pedgoge.kurs_id

Now when I try to Update the grid with an Update command that is fired after an EditCommand finished ( you pres Update as a link) I use it this way :

UPDATE Kurse
SET t1.emertimi=@emertimi,
    kredite=@kredite,
    pershkrim=@pershkrim,
    Viti=@Viti,
    t2.Emertimi as Expr1 = @Expr1, emri=@emri, mbiemri=@mbiemri
FROM Kurse as t1 INNER JOIN 
     Deget as t2 ON t1.degID = t2.Id INNER JOIN
     Pedgoge ON t1.Id = Pedgoge.kurs_id

But this doesn't work. Please as I am a student and non english native is a little hard to understand what should I do in this case. Any help is much appreciated

Vadim Tychonoff · Accepted Answer · 2012-06-27T12:41:33.307

3

UPDATE statement changes only one table or view

It fails because there are two different aliases (t1 and t2 ) in the SET clause

edited Jun 27 '12 at 12:41

answered Jun 27 '12 at 12:35

Vadim Tychonoff

801
5
7

so i have to make three update -s ? I have read in various posts on the internet that you can do it with inner join that is why i am trying. – stevenll Jun 27 '12 at 12:40
you can use an 'inner join' but it has to be only one table that is being updated. To update two tables you absolutely have to issue two UPDATE statements – Vadim Tychonoff Jun 27 '12 at 12:43

score 1 · Answer 2 · answered Jun 27 '12 at 12:45

As Vadim already said, UPDATE command can only change one table. So you'll have to write 3 different Update statements.

UPDATE Kurse
SET emertimi=@emertimi,
    kredite=@kredite,
    pershkrim=@pershkrim,
    Viti=@Viti
WHERE.......

UPDATE Deget 
SET Emertimi = @Expr1, 
    emri=@emri, 
    mbiemri=@mbiemri
WHERE ......

From your query it is not clear what exactly you would like to update and where, so you'll have to sort out where conditions yourself.

Trying to update 3 tables with 1 query [in vain]

2 Answers2