I am having 2 tables. Table1 and Table2. I need to update these 2 tables based on some conditions.
- The SQL select query from Table1 will return multiple records, let’s name it as SQLQuery1
- From SQLQuery1 results returned I need to loop through each record and execute another SQL query from Table2 which will return only 1 record, let’s name it as SQLQuery2
- Update Table1 and Table2 with the results returned from SQLQuery2.
Is there is a single query to combine the above operations?
If you need more information please let me know
UPDATE:
Following are the Sample Queries used,
SELECT E.Id,E.Name from Employee E where E.transdate > '2019-01-201 00:00:01' -- Multiple Results(SQLQuery1) SELECT top 1 ED.Id,ED.balance FROM EmployeeDetails ED where ED.Name= @Name -- Single Results((SQLQuery2) UPDATE Employee set IsProcessed=true where Id=@Id UPDATE EmployeeDetails set IsProcessed=true where Id=@Id
Advertisement
Answer
I would use an UPDATE with an INNER JOIN. I have no idea if it’s best practice at this point in time or whether there is a better way to do this but it works for the example I have created in dbfiddle.
Something like this:
UPDATE E SET IsProcessed = 1 FROM Employee E INNER JOIN EmployeeDetails ED ON E.Id = ED.Id WHERE E.transdate >= '2019-01-20 00:00:01' AND ED.name = 'Martin' UPDATE ED SET IsProcessed = 1 FROM EmployeeDetails ED INNER JOIN Employee E ON E.Id = ED.Id WHERE E.transdate >= '2019-01-20 00:00:01' AND ED.name = 'Martin'
I used >= because I just set all 3 created records to the datetime you have and forgot that looking for a datetime greater than that would provide me with nothing. I also used a hard-coded name Martin instead of using a variable.
Here is my link to the dbfiddle I created in which I created Employee and EmployeeDetails, filled them with test data and then proceeded to identify the correct records using the date, time and name joined by their ID values: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4492b27a9790f34b0bff0996e7fb6d36