×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*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.

# 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!