This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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
 
jQuery in Action, 2nd edition
 
subject: Need help with a SQL statement
 
Similar Threads
How to get table name from oracle query?
A Tricky Stored Procedure
Criteria on joining fails
Adding Indexes is causing Poor Performance
Need Help with a SQL Insert Statement