SQL Update (selecting on SET)

Question

I I need to update the Subject for this whole table adding the Post Code each subject.

However, this information is on a 3rd layer table not connected directly to it. So I have to pass through 1 table to reach the information I need.

Basically is like that

Appointments.table > ClientBooking.Table > ClientInfo

I tried to link those tables but of course, I need to bring 1 result each time here is is where I lack some knowledge

UPDATE Appointments
SET Subject = Subject + (SELECT ZipCode 
                         FROM ClientInfo 
                         INNER JOIN ClientBooking ON ClientInfo.ClientID = ClientBooking.ClientID
                         INNER JOIN Appointments ON ClientBooking.AppointmentID = Appointments.UniqueID
                         WHERE Appointments.UniqueID = "EVERY ID one BY ONe")

Possible duplicate of [SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?](https://stackoverflow.com/questions/707371/sql-update-set-one-column-to-be-equal-to-a-value-in-a-related-table-referenced-b) — Shahroozevsky, Jun 10 '19 at 15:04

score 0 · Answer 1 · answered Jun 10 '19 at 15:09

Just like mentioned HERE, can you do this:

Update Appointments
set Appointments.Subject = Appointments.Subject + (Select ZipCode from ClientInfo 
                                                   INNER JOIN ClientBooking 
                                                   ON ClientInfo.ClientID = ClientBooking.ClientID
                                                   INNER JOIN Appointments AS App
                                                   ON ClientBooking.AppointmentID = App.UniqueID
                                                   where App.UniqueID = Appointments.UniqueID)

Raka · Answer 2 · 2019-06-10T14:57:48.973

-1

Why don't you use Appointments.UniqueID IN ("comma separated id"). This will bulk update all matching rows and will avoid multiple calls.

edited Jun 10 '19 at 14:57

answered Jun 10 '19 at 14:56

Raka

427
2
10

could you exemplify, please? – Murylo Batista Jun 10 '19 at 14:57
Appointments.UniqueID IN (5,10,254). Etc. What is type if UniqueID? – Raka Jun 10 '19 at 14:58
Oh i see what you mean but doing it i would have to type 10000 Ids by hand. each appointment has a unique ID – Murylo Batista Jun 10 '19 at 14:58
How are passing all IDs now? In case you want to update all rows of appointment table, then remove the where condition – Raka Jun 10 '19 at 14:59
I want the query to automatically catch the current Appointment ID he's updating and search for the Post Code – Murylo Batista Jun 10 '19 at 14:59

score -1 · Answer 3 · answered Jun 10 '19 at 15:15

Changed my code for this

SET ANSI_WARNINGS OFF
Update Appointments
set Subject = Appointments.Subject +" - "+ ClientInfo.ZipCode
FROM Appointments
Inner join ClientBooking 
ON Appointments.UniqueID = ClientBooking.AppointmentID
Inner JOIN ClientInfo
ON ClientBooking.ClientID = ClientInfo.ClientID
where Appointments.UniqueID = ClientBooking.AppointmentID
SET ANSI_WARNINGS ON

Now my code works. cheers

SQL Update (selecting on SET)

3 Answers3