Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sql query problem

 
Sudheesh K S
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a table with 4 fields say PK1, A, B, C and D. i want to combine the values of A,B,C and D and store it back to column A for each row with respect to the Primary key.
I am using MySQL as my DB. I tried with inner quiries but not getting the correct query.
Your help and suggestions solicited
Thank you,
Sudheesh K S

[Bear edit: removed 'urgent' from title]
[ November 16, 2005: Message edited by: Bear Bibeault ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Show us the DDL for the table. Without knowing the data types of the fields you intend to combine, we don't know if "combine" means "add together" or "concatenate".

And please, EaseUp.
[ November 16, 2005: Message edited by: Paul Sturrock ]
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64982
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Sudheesh K S",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
 
Tom Blough
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If they are all text, it's as simple as:

UPDATE <tablename> set A = concat(A, concat( B, concat( C, D)));

If they are all numeric, then

UPDATA <tablename> set A = A+B+C+D;

If they are a combination, then you'll have to decide whether to convert the text fields to numeric or vice versa.

Cheers,
 
jiju ka
Ranch Hand
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I have a table with 4 fields say PK1, A, B, C and D. i want to combine the values of A,B,C and D and store it back to column A for each row with respect to the Primary key.
I am using MySQL as my DB. I tried with inner quiries but not getting the correct query.
Your help and suggestions solicited

You are trying to do update. You need to update field A with A+B+C+D. If your table name is T the following query may do. I am not an expert. But check it.

update T t1
set A = (select A concat B concat C concat D
from
T t2
where
t1.pk1 = t2.pk1
);
[ November 16, 2005: Message edited by: jiju ka ]
 
Sudheesh K S
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Sirs,

Thank you very much for your replies... I tried with all the methods and
they are working fine. I was initially complicating the query with sub queries.


With best regards,
Sudheesh K S
 
Sudheesh K S
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jiju,
I tried your query also as i was trying before like this. But i am getting the Error message as given below,

"ERROR 1093: You can't specify target table 'Table Name'for update in FROM clause"

Regards,

Sudheesh K S
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64982
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Sudheesh K S",

My request for you to adjust your display name is not optional! Accounts with invalid display names are closed.

Take a look at the JavaRanch Naming Policy and adjust your display name to match it prior to your next post.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious. Initials for the last name are not sufficient.

bear
Forum Bartender
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic