SQL Update Problem

jotate

I'm gonna simplify what I'm doing, but the problem will be evident.

I execute this query:

SELECT Payment.Payer, Visit.Location
FROM Payment, Visit
WHERE Payment.VisitID = Visit.VisitID
AND Payment.Payer = 'Payer A'
AND Visit.Location = 'Facility A'

It returns a bunch of results, all of which look like ('Payer A', 'Facility A').

The goal here is to update all of the Payment.Payer to be 'Payer B' if the Visit was at 'Facility A' and the Payer was listed as 'Payer B'. So I execute this query:

UPDATE Payment SET Payment.Payer = 'Payer B'
FROM Visit
WHERE Payment.VisitID = Visit.VisitID
AND Payment.Payer = 'Payer A'
AND Visit.Location = 'Facility A'

It succeeds, and I do the previous select query. It returns all ('Payer B', 'Facility A'). Then I ran this query for kicks:

SELECT Payment.Payer, Visit.Location
FROM Payment, Visit
WHERE Payment.VisitID = Visit.VisitID
--AND Payment.Payer = 'Payer A'
--AND Visit.Location = 'Facility A'

It returns all the results from all the Visit Locations. But all the Payers that used to be 'Payer A' are now 'Payer B'. If the payers were something else, it left them unchanged. It's as though it's just ignoring the Visit.Location = 'Facility A' line in the update query, for no reason.

Anyone see the glaring mistake I'm making here? I'm sure it's there, I'm not as adept as I'd like to be with even the simplest queries.

blincoln

I am not a master of SQL, but I think you're going to have to do that in a couple of steps, whether that involves multiple queries, nested queries, or a stored procedure. e.g.:

UPDATE Payment SET Payer = 'Payer B'
WHERE Payer = 'Payer A'
AND VisitID IN 
(SELECT VisitID 
FROM Visit
WHERE Location = 'Facility A')

I am assuming of course that VisitID is a unique key in both tables. If it's not, you'll need to use a field that is.

Evyl

jotate wrote: »

I'm gonna simplify what I'm doing, but the problem will be evident.

I execute this query:

SELECT Payment.Payer, Visit.Location
FROM Payment, Visit
WHERE Payment.VisitID = Visit.VisitID
AND Payment.Payer = 'Payer A'
AND Visit.Location = 'Facility A'

It returns a bunch of results, all of which look like ('Payer A', 'Facility A').

The goal here is to update all of the Payment.Payer to be 'Payer B' if the Visit was at 'Facility A' and the Payer was listed as 'Payer B'. So I execute this query:

UPDATE Payment SET Payment.Payer = 'Payer B'
FROM Visit
WHERE Payment.VisitID = Visit.VisitID
AND Payment.Payer = 'Payer A'
AND Visit.Location = 'Facility A'

It succeeds, and I do the previous select query. It returns all ('Payer B', 'Facility A'). Then I ran this query for kicks:

SELECT Payment.Payer, Visit.Location
FROM Payment, Visit
WHERE Payment.VisitID = Visit.VisitID
--AND Payment.Payer = 'Payer A'
--AND Visit.Location = 'Facility A'

It returns all the results from all the Visit Locations. But all the Payers that used to be 'Payer A' are now 'Payer B'. If the payers were something else, it left them unchanged. It's as though it's just ignoring the Visit.Location = 'Facility A' line in the update query, for no reason.

Anyone see the glaring mistake I'm making here? I'm sure it's there, I'm not as adept as I'd like to be with even the simplest queries.

I'll try and explain this as best I can but i'm not the best teacher. In your update statement there is no relationship between Payment and Visit at the ROW level. When it looks at whether or not to update Row 1 in Payment, what rows need to match between payment and visit in order for it to be true? You haven't told it so it assumes than any match between ANY ROWS of VisitID = Update this row. So even though it looks like your query makes sense relationship-wise, you essentially said Set Payer = 'Payer A' when 1 = 1.

For Example - lets say i'm the database and i'm executing this update statement for you:

Should I update row 1 of Payment? Does 1st row Payment.VisitID = 1st row Visit.VisitID? No. Does 1st row Payment.VisitID = 2nd row Visit.VisitID? Yes. Update Payer on row1.

Should I update row 2 of Payment? Does 1st row Payment.VisitID = 1st row Visit.VisitID? No. Does 1st row Payment.VisitID = 2nd row Visit.VisitID? Yes. Update Payer on row2.

The only time you want to use FROM in an update is if you are using a column from that table in the SET statement. i.e.

SET Payment.Payer = Visit.ColumnWhatever
FROM Visit
Where Payment.VisitID = Visit.VisitID

So yes, Blincoln was 100% correct on his solution - I just think it is important for you to understand why your way wasn't working. I hope that all made sense....

yurnamehere

You can also do an UPDATE on a union:

UPDATE Payment INNER JOIN Visit ON Payment.VisitID = Visit.VisitID
SET Payment.Payer='Payer B'
WHERE Payment.Payer = 'Payer A'
AND Visit.Location = 'Facility A'

vonPoonBurGer

Standard SQL has no facility to update a join, because it makes no sense. The definition of a join is a temporary table that contains data from two tables, joined on one or more conditions. Thus if you update a join, you're updating a temporary table that disappears after the command completes. Not very useful. In most database, what you want to do is use a correlated subquery (blincoln's example). Some DBs have a way of updating a join, but the syntax isn't standard because it's not part of standard SQL, and what they're probably doing under the hood is converting your query into a correlated subquery. In Oracle, that SQL statement results in a syntax error:

SQL> select p.payer, v.location from payment p, visit v where p.visitid = v.visitid;

PAYER                LOCATION
-------------------- --------------------
Payer A              Facility A
Payer A              Facility B
Payer B              Facility B
Payer C              Facility A

4 rows selected.

SQL> UPDATE Payment SET Payment.Payer = 'Payer B'
  2  FROM Visit
  3  WHERE Payment.VisitID = Visit.VisitID
  4  AND Payment.Payer = 'Payer A'
  5  AND Visit.Location = 'Facility A';
FROM Visit
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

If you want to tell us what database you're using, we might be able to find out if it allows UPDATE with a FROM clause. If you want your SQL to be portable, use blincoln's example.

yurnamehere wrote:

You can also do an UPDATE on a union

Not in Oracle you can't. Again, this is nonstandard SQL, and it depends on your database in terms of whether or not that syntax is supported.