[bmistry]

Afternoon

I need a little help with some sql. I have a table within my oracle db with the following columns


Name
Firstname
surname

The name column is not being populated to read the surname and then the Firstname ie it's reading Paul Smith instead of Smith Paul. I need an update sql that will populate the Name Column so the surname is in first followed by a , then the firstname.

A point in the right direction would be great.

Thanks in advance.

[pablolee]

I would question the wisdom of storing the name column at all. I would be inclined to suggest calculating this value when needed.

[bmistry]

We have integrated a few of our systems and this is required.

[pablolee]

Then it is a simple
surname||', '||firstname

[bmistry]

sorry to be a pain but could you send me the full query my sql is not great.

Thanks

[Littlefoot]

Check this example and, if necessary, change it to suit your needs.

SQL> WITH TEST AS
  2    (SELECT 'Paul Smith' name FROM dual
  3     UNION
  4     SELECT 'Little Foot' FROM dual
  5    )
  6    SELECT SUBSTR(name, 1, INSTR(name, ' ')) first_name,
  7           SUBSTR(name, INSTR(name, ' ') + 1, LENGTH(name)) surname
  8    FROM TEST;

FIRST_NAME  SURNAME
----------- -----------
Little      Foot
Paul        Smith

SQL>

[pablolee]

Sorry but no. Have a look at here
It will give you the syntax that you require.
Obviously that was a reply to the OP, not you littlefoot (although I think you may have misunderstood the OP's question)

[beilstwh]

I am not trying to be mean, but if you are not capable of writting such a simple update statement and you do not have anyone else that you can ask at your place of employment, could you tell us what your company is so that I can drop any stock that I might have in it! That being said,

update my_table
set name = surname||', '||firstname;

[bmistry]

I actually work for a Charity who are trying to find a cure for cancer!

[bmistry]

What I also meant to say is that you def wouldnt have bought shares so you got nothing to worry about.

[RBARAER]

Originally Posted by pablolee

I would question the wisdom of storing the name column at all. I would be inclined to suggest calculating this value when needed.

I agree.

Originally Posted by bmistry

We have integrated a few of our systems and this is required.

I would recommend to use a view :

CREATE OR REPLACE VIEW V_MY_TABLE(
    Name,
    FirstName,
    SurName
)
AS
SELECT SurName||', '||FirstName,
    FirstName,
    SurName
FROM My_Table;

Doing so, any insert/update on my_table will only affect firstname and surname (no need to calculate NAME) and the view will always provide the three.

HTH & Regards,

rbaraer