SQL code based on VB code

[Bluejay07]

Hello,

I normally create a VB program to interact with SQL, however, in this case I am trying to avoid creating a program and wondering if it's possible to do this directly in SQL and if so, what code would work.

I want to update one field in one table based on another field in that same table. Here is some pseudo code of what I am trying to accomplish:

"SELECT EMPLOYEE, DISTCODE FROM UPEMPD WHERE EARNDED = 'REG' ORDER BY EMPLOYEE"

Loop
   "SELECT EARNDED, DISTCODE FROM UPEMPD WHERE EMPLOYEE = (employee from first SQL)"
   Loop
      SELECT CASE UPEMPD.Fields("EARNDED")
         CASE "CALL", ...
            UPEMPD.Fields("DISTCODE") = (distcode from first SQL)
            UPEMPD.Update
      END SELECT
   End loop
End loop

As mentioned I normally do not program directly in SQL, however any help would be greatly appreciated.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

I'm having a hard time visualizing your data. Can you post some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Hi George,

I am working with Sage Accpac payroll data in which a single employee can have several deduction codes. The client want to copy the distribution code from an employee and overwrite other distribution codes with that same value. Here are some examples:

Table Example:
EMPLOYEE | EARNDED | DISCTCODE
1001 ACTP PR
1001 BONUS BONUS
1001 CALL DS
1001 REG PR
1001 MEAL PR
1001 OT OT


First loop (based on Earnded = REG:
Employee | Distcode
1001 | PR
1002 | DS

Second loop before update (for employee 1001):
Earnded | Distcode
ACTP | PR
BONUS | BONUS
CALL | DS
REG | PR
MEAL | PR
OT | OT

Second loop after update (for employee 1001):
(Overwriting Distcode for earnded code 'CALL'
Earnded | Distcode
ACTP | PR
BONUS | BONUS
CALL | PR
REG | PR
MEAL | PR
OT | OT

continue through second loop and update other codes based on the values placed in the case clause.
exit second loop
enter second loop with employee 1002
exit first loop

Only certain values in the second loop will be overwritten. I hope this helps.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Nevermind.
I ended up creating a small program to process this. I was just exploring other possibilities on how to accomplish this.

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!