Update a field in a table according to a query results

Question

I wrote this query:

SELECT DISTINCT EMER_EmployeeID FROM (SELECT [EMER_EmployeeID], [EMER_Employee_StatusDateS] FROM [dbo].[EmployeeERP] AS B WHERE [EMER_ClassCode] NOT IN ( 1, 2, 3, 5, 6, 8 ) AND [EMER_Employee_StatusCode] NOT IN ( 3, 4, 5, 6, 68, 0, 1, 9 )) AS t INNER JOIN [dbo].[MonthlySalary] AS K ON K.MS_EmployeeID = T.EMER_EmployeeID WHERE K.MS_Month > 2 + ( CAST(CAST(RIGHT('0' + RTRIM(MONTH([EMER_Employee_StatusDateS])), 2) AS FLOAT) AS INT) )

That give me a list of ID'S.

I have another table - call her TB:

TE_EmployeeID TE_IsCheck

I wrote this to make all TE_IsCheck Zero: Update TB set [TE_IsCheck]= 0

Now, I want to update to 1 the field TE_IsCheck according to list in the 'Big' Query. How can I do that? Thank you!

2 + ( CAST(CAST(RIGHT('0' + RTRIM(MONTH([EMER_Employee_StatusDateS])), 2) AS FLOAT) AS INT) ) seems completely unnecessarily complex. Why not 2 + MONTH([EMER_Employee_StatusDateS])? — Martin Smith
– Martin Smith, Commented Nov 8, 2015 at 15:27
month is an int while StatusDateS is "2015-15-10" so I had to split the month and then convert to int. — oco
– oco, Commented Nov 8, 2015 at 15:34
Try 2 + MONTH([EMER_Employee_StatusDateS]) - It should do the same thing as your convoluted expression. Trimmng integers and adding leading zeroes and casting to float then int isn't going to change the result. — Martin Smith
– Martin Smith, Commented Nov 8, 2015 at 15:38

Gordon Linoff · Accepted Answer · 2015-11-08 15:33:26Z

You can use an update. Say, something like this:

with ids as ( <your query here> ) update tb set TE_IsCheck = 1 where tb.TE_EmployeeID in (select EMER_EmployeeID from ids);

Collectives™ on Stack Overflow

Update a field in a table according to a query results

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related