Update a table with data from a select

[Brandan34]

I have a table that has some poorly formed data. A person can read it and understand it well enough to know what the person entering the data meant. Any program I make has lots of problems with it so I need to fix this data.

I can create a select statement that uses the poorly formed data and with a concatenate and data from other columns in the same record is able to display the data as it should have been entered.

My question, how do I take data from a select statement and use it in an update statement? Or is there a different approach that would be easier to solve this problem? I should only need to do this once so a workaround that may not be standard would be fine.

Thanks

 

[PHV]

A starting point:

UPDATE yourTable
SET PoorlyFormedColumn = PoorlyFormedColumn || OtherColumn
WHERE PoorlyFormedColumn <> PoorlyFormedColumn || OtherColumn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Brandan34]

I was trying what you posted and several variations, when I did not have any luck getting them to work with my database(SQLite3) I posted, thinking that I was overlooking something simple.

What I came up with that worked is just have the select display what I need as SQL, copy it to a batch file then run the batch file with the database. It is not pretty, but it does solve my problem and with luck I will not have to do this again.

Thanks for the sujection. For anyone else who has a problem like this and does not mind a workaround, this is what I came up with.

select "update DataBaseName set PoorlyFormedColumn='" || PoorlyFormedColumn || OtherColumn || "' where rowID=" ||rowID from DataBaseName WHERE PoorlyFormedColumn <> PoorlyFormedColumn || OtherColumn