aspose file tools*
The moose likes JDBC and the fly likes Need help with a SQL statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need help with a SQL statement" Watch "Need help with a SQL statement" New topic
Author

Need help with a SQL statement

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
This might be a little off-topic, but I need to write a SQL statement and I just can't seem to work out the syntax. I thought maybe someone here would be able to help me. Let me try to explain the situation. (By the way, this work is being done inside SQL Server.)
I have two tables, I'll call them src and dest to make things simple. Both tables have a "received" field and an "id" field. For every entry in the src table, I'd like to take the value in the "received" field and insert it into the "received" field of the corresponding record (via the "id" field) in the dest table. Here's the basic SQL I'm trying to use:

This is issuing me an error that looks like this:

Certainly, I can see that I don't use Src in the query, but I'm not entirely sure where it would go. Any ideas you might have would be greatly appreciated.
Thanks,
Corey
[ April 29, 2004: Message edited by: Corey McGlone ]

SCJP Tipline, etc.
James Swan
Ranch Hand

Joined: Jun 26, 2001
Posts: 403
May not work with SQL Server (works on Oracle), but how about trying this:

[ April 29, 2004: Message edited by: James Swan ]
Eric Pascarello
author
Rancher

Joined: Nov 08, 2001
Posts: 15376
    
    6
I think this should do it..

Eric
James Swan
Ranch Hand

Joined: Jun 26, 2001
Posts: 403
Eric, you can't have a "FROM" clause in that position of an update statement, invalid SQL syntax.
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
As promising as that looked, James, it seems to set my Dest.Received values to NULL for all rows.
Perhaps I should also point out that the Src table contains a subset of the records that are contained in Dest. For example, I might have ID 12345 in Dest and not have that record in Src. However, if ID 54321 exists in Src, it does also exist in Dest. The latter is the case in which I'd like to copy the "Received" value from Src to Dest.
[ April 29, 2004: Message edited by: Corey McGlone ]
James Swan
Ranch Hand

Joined: Jun 26, 2001
Posts: 403
Crazy
I modfied the SQL slighty (but keep in mind I am using Oracle).
This is my test bed:
src table

dest_table

new query

Result of dest table:

Can't really say much more if it doesn't work, except for using a "coding" solution instead (ie. not using an "all-in-one" SQL query).
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
I hope that works, James. I'm at home now, but I'll give it a try in the morning when I get back to work. Thanks for the help.
Corey
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Worked liked a charm. Thanks so much for the help, James.
James Swan
Ranch Hand

Joined: Jun 26, 2001
Posts: 403
Happy to help
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help with a SQL statement