Update Statement

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

If the departments are consistent throughout the resultset you could just use

UPDATE TABLEA

SET DEPARTMENT = 240094

WHERE WEEK = 7

iadams SSCommitted Points: 1920 More actions · Answer 2

That would be too easy...

Sorry, their are Many different departements.

-Isaiah

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Does the department bear any relation to the file number?

iadams SSCommitted Points: 1920 More actions · Answer 4

Each FileNumber belongs to a single Departement. (File Number is an Employee).

-Isaiah

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

How about this (test in dev of course)

DECLARE @update table (filenumber int, department int)

INSERT INTO @update

SELECT DISTINCT filenumber, department

FROM TABLEA where department is not null

UPDATE TABLEA

SET department = u.department

FROM @update u inner join TABLEA t on u.filenumber = t.filenumber

WHERE weeknumber = 7

With this check that the department is null, if it's '' change the insert/select.

iadams SSCommitted Points: 1920 More actions · Answer 6

My original postying was a simple example of what I was trying to do. This is the following SQL that I used for the update.

INSERT INTO @update

SELECT DISTINCT FILE_NBR, HOME_DEPARTMENT

FROM PS_AL_CHK_DATA

WHERE (WEEK_NBR = '05')

UPDATE dbo.PS_AL_CHK_DATA

SET HOME_DEPARTMENT = u.HOME_DEPARTMENT

FROM @update u inner join PS_AL_CHK_DATA t on u.FILE_NBR = t.FILE_NBR

WHERE WEEK_NBR = '07'

Thanks a million stacenic for showing me how to build a paramater as a table. Knowing this will help with future updates.

-Isaiah