Update query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update query...

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2007-07-17 : 13:39:27

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 |orgno |acct |newacct
-------------------------------
81 |008/1 |8 |
92 |009/2 |9 |
121 |012/1 |12 |
123 |012/3 |12 |
132 |012/2 |12 |
571 |057/1 |57 |
573 |057/3 |57 |
661 |066/1 |66 |
663 |066/3 |66 |
924 |092/4 |92 |
1001 |100/1 |100 |
1013 |101/3 |101 |

After update table:

newno |orgno |acct |newacct
-------------------------------
81 |008/1 |8 |81
92 |009/2 |9 |92
121 |012/1 |12 |121
123 |012/3 |12 |121
132 |012/2 |12 |121
571 |057/1 |57 |571
573 |057/3 |57 |573
661 |066/1 |66 |661
663 |066/3 |66 |661
924 |092/4 |92 |924
1001 |100/1 |100 |1001
1013 |101/3 |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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 15:53:43

In the neighbourhood of

UPDATE t
SET t.NewAcct = CAST(d.Acct + d.Acct2 AS INT)
FROM Table1 AS t
INNER JOIN (
SELECT LEFT(OrgNo, 3) AS Acct, MIN(RIGHT(OrgNo, 1)) AS Acct2
FROM Table1
GROUP BY LEFT(OrgNo, 3)
) AS d ON d.Acct = LEFT(OrgNo, 3)

Peter Larsson
Helsingborg, Sweden

danielc
Starting Member

49 Posts

Posted - 2007-07-17 : 16:29:06

Peso,

Thank you for the response. It works great but I did notice something from the query. You used orgno, and that field has no bearing on the table other than to create the newno value. For example, after the slash could be any number. It is not necessarly always a 1 or 3. It could be 13, 8 or 6. I only need to use fields newno, acct and newacct. How could I go about it?

Thank you for your help,

Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-18 : 05:19:21

UPDATE t
SET t.NewAcct = d.Acct2
FROM Table1 AS t
INNER JOIN (
SELECT Acct, MIN(NewNo) AS Acct2
FROM Table1
GROUP BY Acct
) AS d ON d.Acct = t.Acct

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources