Update query help...

[elijah06]

Hello all,

I have the following table and would like to perform an update on the newacct field. I need to copy the newno value to newacct. But the caveat is that if a group in acct (like rows having 12's, 57's and 66's) in the acct field then the value of the first newno should be the same for the records in that group. An example in the following table:

Before update table:

newno |acct |newacct
----------------------
81 |8 |null
92 |9 |null
121 |12 |null
123 |12 |null
132 |12 |null
571 |57 |null
573 |57 |null
661 |66 |null
663 |66 |null
924 |92 |null
1001 |100 |null
1013 |101 |null


After update table:

newno |acct |newacct
--------------------
81 |8 |81
92 |9 |92
121 |12 |121
123 |12 |121
132 |12 |121
571 |57 |571
573 |57 |573
661 |66 |661
663 |66 |661
924 |92 |924
1001 |100 |1001
1013 |101 |1013

I thought about counting the groups and if a group is greater then 1 then update newacct with first record number from newno. But that didn't seem to work. Could've been me though... Any ideas?

Thank you for the help,

Dan

 

[alexjones]

Something like

UPDATE a
   SET newacct = b.newno
  FROM myTable as a
  JOIN (SELECT acct, MIN(newno) AS newno
          FROM myTable
         GROUP BY acct) AS b 
    ON b.acct = a.acct

perhaps?

 

[elijah06]

SandieJ,

Thank you for the help. That worked great.

Dan

 

[alexjones]

You're welcome