UPDATE table SET col1 = col2 not working

Hello software engineers,

I'm trying to copy data from one column to another in an Access table using SQL from Delphi and I'm not having any luck.
The following worked in Access:  UPDATE PowerUnits SET [TEMP] = Name;

Tried this and got no error, but no data were copied to the TEMP column:
    with MyQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE PowerUnits SET [TEMP] = Name');
      ExecSQL;
    end;

Tried this and the column was still not copied:
    DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = Name';
    DataMod.CommandQuery.Execute;

I also tried both of the blocks of code above with no brackets around the TEMP column name. BTW, I know that the TEMP column exists, and the only difference between the two columns is that TEMP has Unicode Compression set to YES and Name does not. They are both CHAR(25).

Help, please. I've been floundering for hours! :)
reforestAsked:
Who is Participating?
 
rfwoolfConnect With a Mentor Commented:
The security settings?
What version of access are you using? Vista? (just a joke)
That's strange. But in any case, surely you would get an error in delphi if there was a security problem?
First try and execute the SQL as a query in Access as you have done. When it works, take the exact SQL and try it in Delphi. If you get an error message, report the message. If you get no error message and it works, then try dynamically building the SQL query.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = Name';
Define 'name'.
0
 
reforestAuthor Commented:
Name is a CHAR(25) column that already exists in the PowerUnits table and contains data.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nexusnationCommented:
Name should have brackets too, assuming you're referring to another column in PowerUnits.  The issue is that Name is a reserved keyword in many languages, including (I believe) SQL.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
nexus is correct, Name is likely a reserved word in every database engine, and therefore (1) you'd need to surround it with square brackets [ ], or better yet (2) Rename (no pun intended) the column something else.
0
 
reforestAuthor Commented:
Tried the following, and there are still no data copied to my TEMP column:

    DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = [Name]';
    DataMod.CommandQuery.Execute;
0
 
nexusnationCommented:
Your SQL is correct, and Unicode Compression shouldn't play a factor.  I would think, therefore, that your problem is in your code.  Unfortunately, I don't know Delphi.
0
 
reforestAuthor Commented:
Delphi expert needed. Anyone??
0
 
FactorBCommented:
Few things:
1. Open access file and try to run UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]
2. Check if your Query component (MyQuery) is connected properly and is not in read-only state
3. Is there any peace of code before the one that you gave causing exceptions, consider the option that executable doesn't even reach the Query execution, if there is any code it is recommended to post it here
4. Afterwards it should work:
      MyQuery.Close;
      MyQuery.SQL.Clear;
      MyQuery.SQL.Add('UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]');
      MyQuery.ExecSQL;

Regards B.
0
 
FactorBCommented:
I meant "piece of code" :)
0
 
Geert GOracle dbaCommented:
Occasionally i come accross a similar problem
Using double quotes solved my problem

UPDATE PowerUnits SET "TEMP" = "Name"
0
 
rfwoolfCommented:
Okay I know delphi rather well, and basically whatever SQL will work in Access will work the same if executed from within Delphi depending on your actual database engine.
In any case you need to eliminate the possibilty of using reserve words - so use quotes as someone mentioned above or refer to your fieldnames as descendants of a table as in FactorB's example:
  MyQuery.SQL.Add('UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]');
or try
MyQuery.SQL.Add('UPDATE PowerUnits SET PowerUnits."TEMP" = PowerUnits."Name")

As for NAME being a reserve word - that would be rather sad because I've used it in about 10 tables - but yes you nonetheless need to eliminate the possibility
0
 
reforestAuthor Commented:
It's still not working, even with all the great suggestions. I just noticed something that is probably important. When I try to run an UPDATE query within my Access database, the security settings prevent me from running it. When I tested the UPDATE statement in Access and finally got it to work I forgot that I had to change the security settings before I did it. Is this the real problem? If so, how can I change those settings from Delphi? By the way, I can run all kinds of other queries in Delphi code without this problem (ALTER TABLE, ALTER COLUMN, CREATE TABLE, ADD, and SELECT INTO).
0
 
reforestAuthor Commented:
Increased the points, since I'd really like to figure this out soon...thanks in advance for the help.
0
 
reforestAuthor Commented:
I did try to copy the SQL that worked into my Delphi code, but I tried it again, and it worked. The thing I did differently (occurred to me while I was laying in bed last night) was I commented out all the code after it and ran the program without setting a breakpoint in debug mode. Before, I was opening the database to check it just after the EXECSQL because I didn't want to Delete the Name column if the data weren't copied. This is the code that worked:
    with BlankQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE PowerUnits SET TEMP = Name');
      ExecSQL;
    end;
{code to delete name column and make other db manipulations}

I guess that was it. Thanks for the help everyone.
reforest
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.