Tricky Update statement need help please... 
Author Message
 Tricky Update statement need help please...

Hi all,

I have PersonId values and need to increment by 1.  Any
help would greatly appreciate.
Example: 750 -> 751
         751 -> 752
         ...
Lam

DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
   CustId       INT  NOT NULL,
   PersonId     INT  NOT NULL,
   PersonTpId   INT  NOT NULL,
   PolicyId     INT  NULL
)
GO

INSERT #Temp VALUES (15, 750, 3, 85434)
INSERT #Temp VALUES (15, 751, 3, 51841)
INSERT #Temp VALUES (15, 752, 3, 13841)
INSERT #Temp VALUES (15, 753, 3, 81641)
INSERT #Temp VALUES (15, 754, 3, 23419)
INSERT #Temp VALUES (15, 755, 3, 93187)
GO

SELECT *
  FROM #Temp
go

CustId      PersonId    PersonTpId  PolicyId    
----------- ----------- ----------- -----------
15          750         3           85434
15          751         3           51841
15          752         3           13841
15          753         3           81641
15          754         3           23419
15          755         3           93187

Result want:
-- Want to increment by 1 in PersonId.
CustId      PersonId    PersonTpId  PolicyId    
----------- ----------- ----------- -----------
15          756         3           85434
15          757         3           51841
15          758         3           13841
15          759         3           81641
15          760         3           23419
15          761         3           93187



Sat, 29 Oct 2005 00:39:03 GMT
 Tricky Update statement need help please...

If you want to increment each value by 1, then

UPDATE #Temp
   SET PersonId = PersonId + 1
 WHERE ...

If you want to increment, from the last available PersonId you can :

UPDATE #Temp
   SET PersonId = PersonId + (SELECT COUNT(*)
                                FROM #Temp)
 WHERE ...

--
- Anith
( Please reply to newsgroups only )



Sat, 29 Oct 2005 00:58:48 GMT
 Tricky Update statement need help please...

I would like to update the PersonId in #temp base on the
#source table (PersonId).  Get the max value PersonId in
#Source and increment by 1 in #Temp table.
Please look at the result want below.  Thanks in advance.

DROP TABLE #Source
GO
CREATE TABLE #Source
(
   CustId       INT  NOT NULL,
   PersonId     INT  NOT NULL,
   PersonTpId   INT  NOT NULL,
   PolicyId     INT  NULL
)
GO

INSERT #Source VALUES (15, 750, 3, 93187)
GO

SELECT *
  FROM #Source
GO

CustId      PersonId    PersonTpId  PolicyId    
----------- ----------- ----------- -----------
15          750         3           93187

DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
   CustId       INT  NOT NULL,
   PersonId     INT  NOT NULL,
   PersonTpId   INT  NOT NULL,
   PolicyId     INT  NULL
)
GO

INSERT #Temp VALUES (15, 15, 3, 85434)
INSERT #Temp VALUES (15, 16, 3, 51841)
INSERT #Temp VALUES (15, 17, 3, 13841)
INSERT #Temp VALUES (15, 18, 3, 81641)
INSERT #Temp VALUES (15, 19, 3, 23419)
INSERT #Temp VALUES (15, 20, 3, 93187)
GO

SELECT *
  FROM #Temp
go

CustId      PersonId    PersonTpId  PolicyId    
----------- ----------- ----------- -----------
15          15          3           85434
15          16          3           51841
15          17          3           13841
15          18          3           81641
15          19          3           23419
15          20          3           93187

Result want:
-- Want to increment by 1 in PersonId.
CustId      PersonId    PersonTpId  PolicyId    
----------- ----------- ----------- -----------
15          756         3           85434
15          757         3           51841
15          758         3           13841
15          759         3           81641
15          760         3           23419
15          761         3           93187

Please ignore the text below.

Quote:
>-----Original Message-----
>Hi all,

>I have PersonId values and need to increment by 1.  Any
>help would greatly appreciate.
>Example: 750 -> 751
>         751 -> 752
>         ...
>Lam

>DROP TABLE #Temp
>GO
>CREATE TABLE #Temp
>(
>   CustId       INT  NOT NULL,
>   PersonId     INT  NOT NULL,
>   PersonTpId   INT  NOT NULL,
>   PolicyId     INT  NULL
>)
>GO

>INSERT #Temp VALUES (15, 750, 3, 85434)
>INSERT #Temp VALUES (15, 751, 3, 51841)
>INSERT #Temp VALUES (15, 752, 3, 13841)
>INSERT #Temp VALUES (15, 753, 3, 81641)
>INSERT #Temp VALUES (15, 754, 3, 23419)
>INSERT #Temp VALUES (15, 755, 3, 93187)
>GO

>SELECT *
>  FROM #Temp
>go

>CustId      PersonId    PersonTpId  PolicyId    
>----------- ----------- ----------- -----------
>15          750         3           85434
>15          751         3           51841
>15          752         3           13841
>15          753         3           81641
>15          754         3           23419
>15          755         3           93187

>Result want:
>-- Want to increment by 1 in PersonId.
>CustId      PersonId    PersonTpId  PolicyId    
>----------- ----------- ----------- -----------
>15          756         3           85434
>15          757         3           51841
>15          758         3           13841
>15          759         3           81641
>15          760         3           23419
>15          761         3           93187

>.



Sat, 29 Oct 2005 01:05:49 GMT
 Tricky Update statement need help please...
Is this a short-cut/cleanup you are trying to do? Here is a solution with
all kinds of assumptions on your keys, references etc :

UPDATE #Temp
   SET PersonId = #Source.PersonId +
                (SELECT COUNT(*)
                   FROM #Temp t1
                  WHERE t1.CustId = #Temp.CustId
                    AND t1.PersonTpId = #Temp.PersonTpId) +
                (SELECT COUNT(*)
                   FROM #Temp t1
                  WHERE t1.CustId = #Temp.CustId
                    AND t1.PersonTpId = #Temp.PersonTpId
                    AND t1.PersonId < #Temp.PersonId)
  FROM #Source
 WHERE #Source.CustId = #Temp.CustId
   AND #Source.PersonTpId = #Temp.PersonTpId

--
- Anith
( Please reply to newsgroups only )



Sat, 29 Oct 2005 01:21:26 GMT
 Tricky Update statement need help please...
Anith,

What am I doing without you.  You're so good.  Thank you,
Thank you, Thank you

Lam

Quote:
>-----Original Message-----
>Is this a short-cut/cleanup you are trying to do? Here is
a solution with
>all kinds of assumptions on your keys, references etc :

>UPDATE #Temp
>   SET PersonId = #Source.PersonId +
>                (SELECT COUNT(*)
>                   FROM #Temp t1
>                  WHERE t1.CustId = #Temp.CustId
>                    AND t1.PersonTpId = #Temp.PersonTpId)
+
>                (SELECT COUNT(*)
>                   FROM #Temp t1
>                  WHERE t1.CustId = #Temp.CustId
>                    AND t1.PersonTpId = #Temp.PersonTpId
>                    AND t1.PersonId < #Temp.PersonId)
>  FROM #Source
> WHERE #Source.CustId = #Temp.CustId
>   AND #Source.PersonTpId = #Temp.PersonTpId

>--
>- Anith
>( Please reply to newsgroups only )

>.



Sat, 29 Oct 2005 01:36:47 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Need help with a Tricky Update statement

2. challenge..very tricky sql statement..please help

3. Help with Tricky UPDATE sql statements.

4. Need help with Update statement please...

5. Tricky update statement

6. Tricky Update Statement

7. Help with Update statement please

8. Please help me on Update statement

9. Slow update statement : Help please

10. Update Statement Help - Please

11. Please help me tune this update statement


 
Powered by phpBB® Forum Software