Update statement

Question

Post reply

Update statement

sarwa99

SSC Journeyman

Points: 76
More actions
November 4, 2005 at 8:53 am

#88475

Hello,
I have a table as the following:
Fname   LName   Col1     Col2   Col3       Col4   Col5
John       Tom      A1                 D1                 F2
Bob         Ron      C1                 F1        D2
Josh        Brust                D4                 F1       D4

I want to Arrange it in a way to remove the spaces and bring them to the left . I was using the Update statememt and start from the right to left like to copy the col5 to col4 where col4 is blank, but the problem was like record 3 if col4 has the value , What will be the efficeient way to do it and the data to display as :
Fname   LName   Col1     Col2   Col3       Col4   Col5
John       Tom      A1       D1       F2
Bob         Ron      C1       F1        D2
Josh        Brust    D4       F1       D4

Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Looks like an excel sheet... why don't you normalize the data??

sarwa99 SSC Journeyman Points: 76 More actions · Answer 2

The data is normalized, but those codes are not related together.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

November 7, 2005 at 3:35 pm

#602697

Why would you want to swap the columns then???

Vladan SSC-Insane Points: 22072 More actions · Answer 4

sarwa,

something is wrong with the database design in the posted example. Every column must have certain meaning, you can't simply shake the table as a dustbin to push garbage down and make more room on the top... Please, read a few things about normalization - it certainly is NOT normalized (at least not in the meaning of this word that applies to database design).

For example, you could create 2 new tables: "persons" with columns pers_id, FName, LName and "person_codes" with columns pers_id, code_value. You will then have entry for each person in the persons table, identified by identity column. Value of identity column and the various codes that you have spread over the columns col1-col5 will be entered into the person_codes table (each person will have as many rows in this table as many different codes it has - from 0 to /almost/ infinite, so that you will not be limited by 5 codes as you are now). The queries to do this will be even simpler than if you would try the described shakedown.

This is the best I can make of your description... not guaranteed that it is really the best solution, but surely better than the situation you have now.