• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help with a SQL statement

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
James Swan
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15385
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think this should do it..

Eric
 
James Swan
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Eric, you can't have a "FROM" clause in that position of an update statement, invalid SQL syntax.
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Worked liked a charm. Thanks so much for the help, James.
 
James Swan
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Happy to help
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic