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.