Update query

[scottyfitz]

Hello. I have been trying to update a field using the value of the
same field in a different row. I have an policy table that has a
postal code field. There are several records per policy but not all
have the postal code filled in (the field was just recently added to
the table). I need all the records for each policy to have a postal
code filled in. I have been trying this

UPDATE [Policy-Auto] SET [Policy-Auto].[AGA-CLIENT-POSTAL] = [AGA-
CLIENT-POSTAL]
WHERE (((Exists (SELECT [AGA-CLIENT-POSTAL] from [Policy-Auto] C
WHERE C.[AGA-CLIENT-POSTAL] <> ""))<>False) AND (([Policy-
Auto].PregId)=214310));

but it isn't working. In the above I'm trying to set just one policy.
There are 11 rows for the particular policy I'm testing above (one
with a value and 10 without) but it tells me there are 11 rows going
to be updated (but they are actually just left as they were). I feel
like I'm close but clearly missing something. Any help is much
appreciated.

Scott

 

[KARL DEWEY]

Backup the database and try this --
UPDATE [Policy-Auto] INNER JOIN [Policy-Auto] AS [Policy-Auto_1] ON
[Policy-Auto].regId = [Policy-Auto_1].regId SET
[Policy-Auto].[AGA-CLIENT-POSTAL] = [Policy-Auto_1].[AGA-CLIENT-POSTAL]
WHERE ((([Policy-Auto].[AGA-CLIENT-POSTAL]) Is Null) AND
(([Policy-Auto_1].[AGA-CLIENT-POSTAL]) Is Not Null));

 

[scottyfitz]

Backup the database and try this --
UPDATE [Policy-Auto] INNER JOIN [Policy-Auto] AS [Policy-Auto_1] ON
[Policy-Auto].regId = [Policy-Auto_1].regId SET
[Policy-Auto].[AGA-CLIENT-POSTAL] = [Policy-Auto_1].[AGA-CLIENT-POSTAL]
WHERE ((([Policy-Auto].[AGA-CLIENT-POSTAL]) Is Null) AND
(([Policy-Auto_1].[AGA-CLIENT-POSTAL]) Is Not Null));

--
KARL DEWEY
Build a little - Test a little

Hello. I have been trying to update a field using the value of the
same field in a different row. I have an policy table that has a
postal code field. There are several records per policy but not all
have the postal code filled in (the field was just recently added to
the table). I need all the records for each policy to have a postal
code filled in. I have been trying this

UPDATE [Policy-Auto] SET [Policy-Auto].[AGA-CLIENT-POSTAL] = [AGA-
CLIENT-POSTAL]
WHERE (((Exists (SELECT [AGA-CLIENT-POSTAL] from [Policy-Auto] C
WHERE C.[AGA-CLIENT-POSTAL] <> ""))<>False) AND (([Policy-
Auto].PregId)=214310));

but it isn't working. In the above I'm trying to set just one policy.
There are 11 rows for the particular policy I'm testing above (one
with a value and 10 without) but it tells me there are 11 rows going
to be updated (but they are actually just left as they were). I feel
like I'm close but clearly missing something. Any help is much
appreciated.

Scott- Hide quoted text -

- Show quoted text -

Hi Karl. This works perfectly. Thank you very much for your help. I
really appreciate it.

Scott