Some of you would be so kind to explain to me how this SQL query is working, I understand it in part but not in its entirety. I clarify that it only affects two tables. Thank you
UPDATE PRODUCTO SET Cantidad = T2.stock
FROM PRODUCTO T1
INNER JOIN (
SELECT T3.id, T3.Cantidad - T4.Cantidad Stock
FROM PRODUCTO T3
INNER JOIN compra T4 ON T3.id = T4.id
) T2 ON T1.id = T2.id WHERE t2.id = 2
what I understand is: UPDATE to table PRODUCT SET column QUANTITY = ??? from the PRODUCT table t1, I understand it is a reference to PRODUCT?? INNER JOIN who is T3? what I understand is table3.id, it refers to T3 - T4 ????
FROM product and now refers to it as T3?? INNER JOIN column BUY t4?? can you refer columns too??
So better explain it to me. :)
I try to answer you
What you have is an update query that has a nested query.
To your question T3 is an alias, when you put another name behind a table, for example
SELECT * FROM mitabla tabla1;
, from there we could say that the tablemitabla
is no longer calledmitabla
for that select but insteadtabla1
. To make a query it should beSELECT tabla1.campo FROM mitabla tabla1;
My interpretation of the query:
Update the product table put as much quantity as T2.stock.
T2.stock will take it out of the result of the nested query.
The nested query says select T3.id (where T3 is product), t3.quantity-t4.quantity (where t4 is the shopping table) you call the result of this operation stock (another alias).
And to know which fields you have to join you have to take into account the following filter: T1 joined with T2 when T1.id (product.id) = t2.id (the result of the query.id)
Finally, the t2.id (which we could translate as product.id of the subquery) must be = a 2.
What you have to look for is information on the concept of aliases and nested queries.
I hope I have answered you
All the best!
I think it's easier if you try to interpret it from the inside out (in parts).
So first we get this:
This is a query that returns a table with 2 fields
ID
andSTOCK
two values and 1...N records.We put this in parentheses and apply an alias
T2
to it, so from now on when you talk aboutT2
, think of that table.If you take the outer query and put
T2
, it would be something like this:This query updates the
CANTIDAD
of the tableT1
with what is in the fieldSTOCK
of the tableT2
, for theID=2
.The only special thing is that the table
T2
, instead of being a physical table in the Database, has been mounted "on the fly" , so to speak, from the previous select (the one inside the parentheses).