Change query to update.

[mentasses]

I have created a select query:

SELECT a.STAFFNO,a.REGION,a.DEPARTMENT,
LEFT(REGION,3) AS REGCODE,
LEFT(DEPARTMENT,4) AS DEPTCODE,
(DEPTCODE+REGCODE) AS ACCODE,
(DEPTCODE+REGCODE) AS COSTCNTRE
FROM ELYOSERVICESTEST.dbo.MASTER a
WHERE STATUS = 'Active'

I want to change this so that it updates the REGCODE,DEPTCODE,ACCODE and COSTCNTRE fields every night but am not sure how to change the code to do this.Can anyone help?

 

[swampBoogie]

Why update? You could create a view with the calculations so you don't need to write them out.

 

[RileyCat]

mentasses --

I would need more information on what exactly it is you're trying to do. But, if you're just looking to update the data in an existing table, use the following update format:

update     ELYOSERVICESTEST.dbo.MASTER a
set        REGCODE = new value,
           DEPTCODE = new value,
           ACCODE = new value,
           COSTCNTRE = new value
where      STATUS = 'Active'

Please elaborate further on what it is you're trying to do if this doesn't answer your question.

Good luck!

 

[SQLBill]

What exactly are you trying to do? You say you want to UPDATE certain fields every night. WHy is the data changing? A department code (I assume that's what DEPTCODE is short for) shouldn't be changing once it's entered. Same with the rest of the fields.

Or are you wanting to INSERT new data?

-SQLBill

 

[mentasses]

Users enter a REGION and DEPARTMENT from a pick list in the SQL database for each employee. The format of these is:

REGION: abc - Midlands - Birmingham
def - London - Dagenham

DEPARTMENT: 1234 - Engineer
5678 - Administrator

I need to update the ACCODE & COSTCNTRE fields with just the first part of the codes:
1234abc, 5678abc, 1234def, etc
But only for 'Active' employees, as the data can change regularly I need to run the update each night.