File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Sql query problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sql query problem" Watch "Sql query problem" New topic
Author

Sql query problem

Sudheesh K S
Greenhorn

Joined: Feb 25, 2005
Posts: 8
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

Joined: Apr 14, 2004
Posts: 10336

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61092
    
  66

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
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,


Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
jiju ka
Ranch Hand

Joined: Oct 12, 2004
Posts: 306

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

Joined: Feb 25, 2005
Posts: 8
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

Joined: Feb 25, 2005
Posts: 8
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

Joined: Jan 10, 2002
Posts: 61092
    
  66

"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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sql query problem