help making a query to update a field using 3 tables

[Luiz Eduh]

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

 

[bborissov]

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

 

[Olaf Doschke]

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.