0

I am running a query to apply a number depending on the WHERE clause which verifies information from different tables

UPDATE EstadoDoc  
SET PuntajePrevio = 10
FROM Investigador i, Autores a, Documentos d, EstadoDoc e, Periodo p
WHERE i.IdInv = a.IdInv 
  AND a.IdDoc = d.IdDoc  
  AND d.Tipo = 'AR'   
  AND e.Estado IN ('A', 'RyR')
  AND p.FechaDesde <= e.FechaEst 
  AND p.FechaHasta >= e.FechaEst 
  AND p.IdPeriodo = 2019

This is the query I'm running and it's supposed to put the value 10 in PuntajePrevio depending on the WHERE clause, but I'm noticing it's not working correctly because it is applying the value to 2 column were it shouldn't.

To further explain here is a screenshot of the data it affected - it should be only taking into consideration all the ones that have d.tipo = 'AR' but is it not.

As you can see it affected the rows with IdDoc =012, 000 and 005 in the table EstadoDoc

But as you can see in the table Documentos the rows with IdDoc=012 and 000 have tipo = AC and not AR, im wondering why is it still taking them in consideration even when the clause specifies tipo='AR'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alex schobel
  • 87
  • 2
  • 11
  • Consumable sample data, as DDL and DML statements, not images, will help us help you here. – Thom A Aug 24 '21 at 14:02
  • 7
    Also, please use modern `JOIN` syntax, this is old and confusing. – DavidG Aug 24 '21 at 14:03
  • what is Consumable sample data, as DDL and DML statements? – alex schobel Aug 24 '21 at 14:04
  • 6
    If you switch to using `JOIN` rather than using commas in your `FROM` clause, you'd probably notice that there's **nothing** correlating your `i`, `a` and `d` tables with the `e` and `p` tables. That's probably a mistake. – Damien_The_Unbeliever Aug 24 '21 at 14:08
  • 1
    [What are DDL and DML statements?](https://www.google.co.uk/search?q=What+are+DDL+and+DML+statements%3F) As someone writing SQL, you should know what both of these are. – Thom A Aug 24 '21 at 14:12
  • 4
    Given you're updating `e`, and the lack of correlation from any of `i`, `a` or `d`, it should come as no surprise that a condition on `d` doesn't change which `e` rows are affected. – Damien_The_Unbeliever Aug 24 '21 at 14:15
  • Can you run your select query using your "where" clause. Check whether your are getting expected records for update or not. – Vivek Aug 24 '21 at 14:22

0 Answers0