How to update a query

Question

How can I update some fields of a select? (something like the following)

update (select *
        from Students s
        join Father f on s.fId=f.Id
        where s.Id=12)
set f.FirstName='John'

Please help. Thank you.

Which RDBMS is this for? – ypercubeᵀᴹ Apr 08 '13 at 14:16 — ypercubeᵀᴹ, Apr 08 '13 at 14:16

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

Try:

Update Students
set FirstName='John'
where Id=12

No need of a select in this case. Check the link SQL UPDATE Statement for more details.

In your case you can use SQL Server - inner join when updating

UPDATE f
SET FirstName='John'
FROM Students s JOIN Father f 
    ON s.fId=f.Id 
WHERE s.Id=12

edited May 23 '17 at 10:25

Community

1
1

answered Apr 08 '13 at 09:20

TechDo

18,398
3
51
64

`FirstName` belongs to which table? – TechDo Apr 08 '13 at 09:29
It belongs to table Father. – user543489 Apr 08 '13 at 09:34
Note that the update-with-join syntax is different for different DBMSes. I see nothing indicating that MS SQL Server is the right technology in this case. – IMSoP May 05 '13 at 18:23

score 0 · Answer 2 · answered Apr 08 '13 at 09:21

0

UPDATE Students
SET FirstName='John'
WHERE ID = 12

That should work for you :)

answered Apr 08 '13 at 09:21

wotney

1,039
3
21
34

score 0 · Answer 3 · edited Apr 08 '13 at 09:43

0

To update FirstName of a Student with a specific Id do the following:

update Students 
set FirstName='John'
where Id=12

edited Apr 08 '13 at 09:43

Daniel Hedberg

5,677
4
36
61

answered Apr 08 '13 at 09:21

Kapil Khandelwal

15,958
2
45
52

score 0 · Answer 4 · answered Apr 08 '13 at 09:40

0

You can use a Join update for this kind of situation. for a example if you need to set the fathers surname for his son, You can use a query like this.

UPDATE SonsDetails SET Surname = Father.Surname
FROM [dbo].[SonDetails] AS SonsDetails INNER JOIN [dbo].[FatherDetails] Father 
ON [SonsDetails].[FatherId] = Father.Id

answered Apr 08 '13 at 09:40

SAM

825
1
7
15

Note that this syntax will be different for different DBMSes, because it is not in the SQL standard. The example you gives looks like MS SQL Server to me, but there's no clue in the question that this is the system the questioner is using. – IMSoP May 05 '13 at 18:21

How to update a query

4 Answers4