UPDATE column 1

[lb1]

I need to update a column, by copying part of another column.

I plan to use a query such as:

UPDATE MAIN SET MAIN.ABBREVIATION = left(FULLDat,125);

This statement work fine when the column(FULLDat) is not a text datatype.

How can I change the above statement to be used with a text datatype column?

This query will be run from T_SQL

If, there is no way to do it this way, is there another?

Thanks in Advance.

Louiis

 

[SteveMe]

UPDATE MAIN SET MAIN.ABBREVIATION = left(cast(FULLDat as varchar(100),125);

 

[Sqlsa]

Hi,
Use SUSBSTRING instead of LEFT

UPDATE MAIN SET MAIN.ABBREVIATION = rtrim(SUBSTRING(FULLDat,1,125))

But I noticed one thing. If Fulldat has NULL value in it, then Main.Abbreviation also becomes NULL.

I believe you want to use this abbreviation for GROUP BY.

(I know this because I am the person who gave you this ABBREVIATION idea.)

If this does not effect your query, it is ok.

Otherwise you can use one more update statement to update the value of abbreviation to 'NoTextData' or some abbreviation you prefer.

UPDATE MAIN SET MAIN.ABBREVIATION = 'NoTextData'
WHERE Fulldat is not null

This will see that you get all the records included in your GROUP BY query.

So in your INSERT statements, if there is no value for FULLDAT coulmn, then set it's ABBREVIATION column value to
'NoTextData' or whatever abbreviation you want to follow
in the above UPDATE statement.
OR Before trying this GROUP BY query, Use the UPDATE statement to update the ABBREVIATION for all those records with FullDat as NULL.


Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[lb1]

Sreenivas

I will try this.
This process is to update all the existing data. For the new one, we automatically generate the abbreviation data and this is not a problem.

---- Just Tried ---
It's is working fine. Just saved us a lot of work.

Thanks again.

Louis

 

[Sqlsa]

Hi Louis,

The update statement to take care of records with Fulldat as Null should read as

UPDATE MAIN SET MAIN.ABBREVIATION = 'NoTextData'
WHERE Fulldat is null

(NOT is ommitted from the above WHERE clause)

Sorry for the correction.

The WHERE clause should not have "not". This is to be done for the existing data. If there is NULL value in ABBREVIATION column and if you perform a GROUP BY based on ABBREVIATION column, the records with NULL value in this particular column wil not be considered.

So , please make sure that all the records are included in your query.

Hope this helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[lb1]

I did see this and corrected it - Thanks for your concern.
Rgds.
Louis