CODEWITHSUNDEEP
sqlsql-serverinsertcursor
zgall1
zgall1

Reputation: 3025

Modifying a SQL table to insert data from an existing row into the previous row

I have a SQL table called RawData with the following six columns (all type VARCHAR):

Name StreetAddress State Phone Website Email

I import new data from a CSV file into this table on a regular basis and 99% of it is formatted correctly. For 1% of the data, for reasons that I understand but are not important for this question, the data is imported such that all of the first five columns (Name, StreetAddress, State, Phone, Website) have data that is formatted correctly but the sixth column Email is blank. Furthermore, the email is imported into its own row immediately below this "problem" row but the email is placed in the Name column. These situations are readily identified begins the email address is always inserted in the Name column with the mailto: prefix .

What I would like to do is devise a query to take the incorrectly placed email address from the Name, strip the mailto: prefix and then place it into the Email column in the previous row. I would then like to delete the row with the 'mailto:' email in the `Name' column.

I've done a bit of research and believe that I need to use a cursor to copy the data from the row below the correct row and insert it into the Email column but having never used cursors before, I am struggling to come up with anything. Any help would be appreciated.

Edit: For the purposes of brevity, I omitted that the table does contain an identity column ID.

Upvotes: 0

Views: 153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In SQL Server, there is no concept of "previous" row. You are going to need to add an identity column to the table so you can identify these situations. Fortunately, you can insert your data into a view, so the identity is automatically incremented in the load order.

Once you have the id, you can use an update:

update nd
    set nd.email = stuff(ndnext.name, 1, 7, '')
    from newdata nd join
         newdata ndnext
         on nd.id = ndnext.id - 1
    where ndnext.name like 'mailto:%';

Once you have verified that this is correct, you probably want to do:

delete nd from nd
    where nd.name like 'mailto:%';

Upvotes: 2

Related Questions