×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

help making a query to update a field using 3 tables

help making a query to update a field using 3 tables

help making a query to update a field using 3 tables

(OP)
Hello, I need help from the experts making a query

Here's my scenario;
I have a table where I want to update the prices column from another table but only for a specific date range from a 3rd table, something like this;

update table1.price from table2.cost where table1.transnum = table3.transnum and also only when table3.transdate = date(2018,01,30)

I Can't figure out how to connect all these 3 tables.
Table1 and table2 have a same field named prime that can be used to filter the data. Table3 and Table1 can be connected by a field named transnum both tables have it and maybe filtered only when transdate = the date that I need.

Any suggestion will be much appreciated

RE: help making a query to update a field using 3 tables

CODE

update table1 SET price = table2.cost;
FROM table1;
INNER JOIN table3 ON table1.transnum = table3.transnum and table3.transdate = date(2018,01,30);
INNER JOIN table2 ON table1.prime = table2.prime 

But I'm not sure that this is the right query.
If you post some example data from all the tables and desired result from this data that it will be easier for us.


Borislav Borissov
VFP9 SP2, SQL Server

RE: help making a query to update a field using 3 tables

I second the last sentence, there is no one size fits all 3 tables recipe like the general solution to quadratic equations.
Factors playing a role are the cardinality of data. Every time you do a 1:n join for an update on the 1 side, you'll likely get unexpected results.
You need to know the hierarchy and whether you are updating the 1 or N or M side of things. The tables and their related fields won't tell that alone without seeing into data, from the statistical point of view it gets more and more likely you need to pick some record more specifically, not just for the source value, also for the target row. These two must have 1:1 matches and PK/FK don't tell about that cardinality.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close