Post reply

Update within/after Union

  • December 2, 2014 at 8:56 am

    #99987

    I have a view which is selecting several columns from table "A" and then doing a Union against table "B" where it is also selecting several columns of data. I would like to make one small change to the view where after the data is being selected in both tables it will modify the column which has the phone data and add the prefix (212) to it.

    select fname, lname, city, phone from tableA

    union

    select fname, lname, city, state, phone from tableB

    result : John Doe Albany NY 124-4567

    John Deer Troy NY 555-1212

    John Smith Queens NY 555-8043

    I would like the new data to be:

    John Doe Albany NY (212)124-4567

    John Deer Troy NY (212)555-1212

    John Smith Queens NY (212)555-8043

    Can I have the update statement within the UNION (where everything is done in one unit of work/transaction or after the union statement dump the data into a temp table and then update my column.

  • December 2, 2014 at 9:04 am

    #1762128

    Are you modifying the view or updating the source tables?

    If you're just modifying the results returned in the view, and assuming the the phone column is a varchar the code below will amend the column.

    '(212)'+phone

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply