UPDATE table based on query

Question

I have not used UPDATE before and I am trying to update a table based on a query of multiple tables. What I am trying to do is change the servicetype.servicetype_id from 1 to 74571 on the services returned in my query. When I execute the query below I get an error of "The-multipart identifier "service_1.service_id" could not be bound" and all the instances of the service table in my query change to service_1.

UPDATE service
SET service.service_id = 74571
FROM accountservice INNER JOIN
     serviceclass ON accountservice.serviceclass_id = serviceclass.serviceclass_id INNER JOIN
     service ON accountservice.service_id = service.service_id INNER JOIN
     servicetype ON service.servicetype_id = servicetype.servicetype_id
WHERE        (servicetype.servicetype_id = 1) AND (serviceclass.serviceclass_id = 74561) AND (accountservice.fromdate <= '2013-11-15') AND (accountservice.todate IS NULL)

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

1

You can specify which table to update in a join like so:

UPDATE service
SET service.service_id = 74571
FROM accountservice INNER JOIN
         serviceclass ON accountservice.serviceclass_id = serviceclass.serviceclass_id INNER JOIN
         service ON accountservice.service_id = service.service_id INNER JOIN
         servicetype ON service.servicetype_id = servicetype.servicetype_id
WHERE ...

Be careful with this! You should definitely confirm the results of the SELECT before changing it to an UPDATE.

Here are answers to a similar question... How do I UPDATE from a SELECT in SQL Server?

edited May 23 '17 at 10:31

Community

1
1

answered Feb 21 '14 at 14:36

Manny

967
1
6
17

I'm getting an error with this. It says "The-multipart identifier "service_1.service_id" could not be bound. It changes service to service_1 throughout the query when I execute it. – Stedman Feb 21 '14 at 14:48
@Stedman Can you update your question with the `UPDATE` query? You probably simply have an alias in error or missing somewhere. Try `UPDATE service_1...` if the table has been aliased (`service AS service_1`). – Manny Feb 21 '14 at 14:56

score 0 · Answer 2 · answered Feb 21 '14 at 14:35

Not tested, but this should do the trick.

UPDATE s SET s.service_id = 74571 FROM
FROM            accountservice INNER JOIN
                     serviceclass ON accountservice.serviceclass_id = serviceclass.serviceclass_id INNER JOIN
                     service s ON accountservice.service_id = service.service_id INNER JOIN
                     servicetype ON service.servicetype_id = servicetype.servicetype_id
WHERE        (servicetype.servicetype_id = 1) AND (serviceclass.serviceclass_id = 74561) AND (accountservice.fromdate <= '2013-11-15') AND (accountservice.todate IS NULL)

UPDATE table based on query

2 Answers2