Updating only first occurence of an id in SQL

Question

Due to an added field in a MsSql Database, I have to change the records in a table from NULL to something more usefull.

I've got a table with addresses. Because customers have multiple addresses we'd like to mark certain addresses as default and others as something like shipping.

How can I, using a query, change only the first of every address to "Default" and every other to "Shipping"? Every address has a foreign key relation to the Customers table thru a CustomerId.

It doesn't really matter which one is made default, as long as one is default and the rest something else. Just to be able to edit the default address in thru the UI of our application and show a table with every other adress.

Answer 1

Depends what is first. Do you rows have such a concept such as date etc to order them. Remember a query will use zero or more indexes to get to your data. The ordering of these is becomes a factor also. If you drop the indexes then the order the data will will be the only factor.

Answer 2

Assuming that Address table uses a surrogate primary key that is integer based (like an IDENTITY column), and if you really don't care which one gets marked as DEFAULT, you could do something like the following (which updates the row with the lowest integer value as the DEFAULT).

DECLARE @Addresses TABLE
    (
      AddressID INT ,
      CustomerID INT ,
      AddressType VARCHAR(8)
    )

INSERT  INTO @Addresses
        ( AddressID ,
          CustomerID ,
          AddressType
        )
        SELECT  1 ,
                1 ,
                NULL --Client 1 has 3 addresss
        UNION ALL
        SELECT  2 ,
                1 ,
                NULL
        UNION ALL
        SELECT  3 ,
                1 ,
                NULL
        UNION ALL
        SELECT  4 ,
                2 ,
                NULL --Client 2 has 1 addresses
        UNION ALL
        SELECT  5 ,
                3 ,
                NULL --Client 3 has 2 addresses
        UNION ALL
        SELECT  6 ,
                3 ,
                NULL

SELECT  *
FROM    @Addresses a

--update an arbitrary address ASSUMING that you used an integer for a surrogate primary key
UPDATE  @Addresses
SET     AddressType = CASE WHEN AddressID IN ( SELECT   MIN(AddressID)
                                               FROM     @Addresses a
                                               GROUP BY a.CustomerID ) THEN 'Default'
                           ELSE 'Shipping'
                      END


SELECT  *
FROM    @Addresses a