CODEWITHSUNDEEP
sqlsql-server
OM Asphyxiate
OM Asphyxiate

Reputation: 329

Updating column value using query & join

I'm attempting to establish a date when a transaction actually occurred in my DB since the system used tracks by transaction ID and not a date. The following query is what I've attempted, which ran, but did not update any values:

UPDATE dbo.transactions
SET t.time_ran=e.time_ran
FROM transactions t
CROSS JOIN eod_master e
WHERE(t.clinic=e.clinic) AND (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)

I have tested the following query to make sure that the value I am looking for is correct without any issues:

SELECT t.tran_num, t.clinic, e.time_ran, t.date_entered, t.clinic
FROM transactions t
CROSS JOIN eod_master e
WHERE(t.clinic=e.clinic) AND (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)

Since there weren't any errors I'm not sure why the values aren't being updated. I did run into an error with my t.time_ranthrowing an error The multi-part identifier "t.time_ran" could not be bound. But I got around it by getting rid of the alias for that table when testing.

UPDATE:

Just to clarify how the tables are setup. I have 35 different clinic values and each of them have a start_tran_num and end_tran_num for each day of business. time_ran is the datetime of when the end of day was established. The start and end declarations won't overlap at the same clinic, IE: day one starts at 1, ends at 200, day 2 automatically starts at 201, etc.

enter image description here

Here is a mock-up example of what the data is like. I've attempted the following code after all the provided insight without any changes. There are no NULL values in the eod_master.time_ran column and currently all transations.time_ran are null so the query shouldn't be attempting to put in the same values that already exist.

UPDATE transactions
SET time_ran=e.time_ran
FROM transactions t
INNER JOIN eod_master e
ON e.clinic=t.clinic
WHERE (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)

Upvotes: 2

Views: 58

Answers (2)

Jericho
Jericho

Reputation: 213

Could you just try using the Alias in the UPDATE statement, so:

UPDATE
  t
SET
  t.time_ran=e.time_ran
FROM
  transactions t
  INNER JOIN eod_master e ON
    t.clinic=e.clinic
WHERE
  t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num;

I've had similiar issues when UPDATING joins.

Upvotes: 1

David Manheim
David Manheim

Reputation: 2626

A cross join (can) return multiple results for each row; you aren't going to be able to update to set it equal to more than 1 value, so you need to use a regular join, not a cross join. Given that you are doing this with a range, you may need to use a subquery to find a single transaction that matches, and update from that.

Upvotes: 0

Related Questions