CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11931

SQL Update SET sub query?

I have an SQL query like so:

UPDATE ScheduleTasks  
SET 
AssignedResource='360 CLEAN'
WHERE (ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Job_No = 'ABC123')) 
AND Description = 'Filing'

my issue is that I have another table called Resource with a Resource_ID and Resource_Name what I am trying to do is update the scheduleTasks table with the Resource_ID and not the Resource_Name which is what 360 CLEAN is, its Resource_Name not Resource_ID my question is how can I get the Resource_ID From the Resource_Name

Upvotes: 4

Views: 322

Answers (3)

Linger
Linger

Reputation: 15058

If you want to update all of the records in the ScheduleTasks (which I recommend) then use:

UPDATE ST
SET AssignedResource = R.Resource_ID
FROM ScheduleTasks ST
  INNER JOIN Resource R ON R.Resource_Name = ST.AssignedResource

If you want to limit what you update then add a WHERE clause like so:

UPDATE ST
SET AssignedResource = R.Resource_ID
FROM ScheduleTasks ST
  INNER JOIN Resource R ON R.Resource_Name = ST.AssignedResource 
  INNER JOIN Schedule S ON S.ScheduleID = ST.ScheduleID 
WHERE ST.AssignedResource = '360 CLEAN'
   AND ST.Description = 'Filing'
   AND S.Job_No = 'ABC123'

Upvotes: 0

John Odom
John Odom

Reputation: 1213

You can do this by using an INNER JOIN on the UPDATE statement like so:

UPDATE st SET
    AssignedResource = r.Resource_ID
FROM ScheduleTasks st
    -- change join conditions to your preference
    INNER JOIN Resource r on r.Resource_Name = '360 CLEAN'     
WHERE st.Description = 'Filing' AND EXISTS
(
    SELECT s.ScheduleID 
    FROM Schedule s
    WHERE s.Job_No = 'ABC123' AND st.ScheduleID = s.ScheduleID
)

Also updated your query for optimizations on checking if the ScheduleID from ScheduleTasks exists in the Schedule table.

Upvotes: 4

Pரதீப்
Pரதீப்

Reputation: 93754

Try using a sub-query

UPDATE ScheduleTasks
SET    AssignedResource = (SELECT TOP 1 Resource_ID
                           FROM   Resource
                           WHERE  Resource_Name = '360 CLEAN')
WHERE  st.ScheduleID = (SELECT ScheduleID
                        FROM   Schedule
                        WHERE  Job_No = 'ABC123')
       AND st.Description = 'Filing'

Upvotes: 2

Related Questions