How to update a record by checking a value in another record in same table

Question

How to update a record by checking a value in another record in same table

Saranabi

SSC Veteran

Points: 217
More actions
October 22, 2019 at 5:15 pm

#3691064
I have a table:
```
ID    DATE           RANGE      STATUS
--------------------------------------------
123   30-SEP-2019    000        C
123   30-SEP-2019    001        NULL
345   25-SEP-2019    000        C
345   30-SEP-2019    001        NULL
567   30-SEP-2019    000        C
567   30-SEP-2019    001        NULL
789   27-SEP-2019    000        C
789   30-SEP-2019    001        NULL
```
Records with the same ID (for ex., 123) considered as same record with different RANGE. I need to update the STATUS of 001 RANGE records to Y for which 000 RANGE record should have the date as 30-SEP-2019.
Ex. Update query should update the STATUS to 'Y' for the record with the ID - '123' and RANGE - '001', because for the ID - '123' with the RANGE - '000' record's DATE is '30-SEP-2019'. Also it should update the STATUS of 001 RANGE record with the ID - 567.
It should not update the STATUS of 001 RANGE for the IDs 789 & 345, because those 000 RANGE record's DATE is not equal to 30-SEP-2019.
How can I achieve this?
- This topic was modified 5 years, 1 month ago by Saranabi.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76989 More actions · Answer 1

When posting to an international forum, it is best to use a date that is internationally recognized. ISO suggests using YYYYMMDD, but YYYY-MM-DD is acceptable alternative.

What is so special about 20190930 that you want to use the values from that date in perpetuity? I suspect that it's not that special and you want to rethink why you are picking that record.

Also, you may have a table, but you've posted a fixed-width text file. You'll get more help if you post a script to first create a (temporary) table and then insert values in said table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Collins SSCrazy Points: 2160 More actions · Answer 2

I can't test it (don't have the table) and all of your column names are reserved words so here's a swag at it:

with range_cte(ID) as (
select
  ID
from
  YourTable
where
  [DATE]='2019-09-30'
  and [RANGE]='000')
update yt
set
  [STATUS]='Y'
from
  YourTable yt
 join
  range_cte rc on yt.ID=rc.ID
where
  yt.[RANGE]='001';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

That sounds like a pretty easy task:

WITH cte AS (
SELECT [RANGE], [STATUS], LAG([DATE]) OVER (PARTITION BY [ID] ORDER BY [RANGE]) AS [PrevDATE]
FROM <table name>
)
UPDATE [cte]
SET [STATUS]='Y'
WHERE [RANGE]='001' AND [PrevDate]='30-SEP-2019'

That meets your requirements I believe. Might not be the most elegant solution, but it short and easy to follow what is going on and also easy to look at the data.

I recommend tossing it in a transaction and rolling things back rather than letting that go live without proper testing, but it worked on my system. Replace <table name> with the name of your table.

EDIT - modified the code a bit as there was some redundant code in it. Might as well not pull in data you aren't going to be looking at and don't need.

This reply was modified 5 years, 1 month ago by Mr. Brian Gale. Reason: Code cleanup - was pulling data that is never used

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

drew.allen SSC Guru Points: 76989 More actions · Answer 4

Mr. Brian Gale wrote:

WITH cte AS (
SELECT [RANGE], [STATUS], LAG([DATE]) OVER (PARTITION BY [ID] ORDER BY [RANGE]) AS [PrevDATE]
FROM <table name>
)
UPDATE [cte]
SET [STATUS]='Y'
WHERE [RANGE]='001' AND [PrevDate]='30-SEP-2019'

I think this is close, but I don't like the fact that the date is hard-coded, because I think what's important is that the date for range 000 MATCHES the date for range 001. If the OP had answered my question, that would be more obvious. Here is how I would rewrite this query.

WITH cte AS (
    SELECT [RANGE], [STATUS], LAG([RANGE]) OVER (PARTITION BY [ID], [DATE] ORDER BY [RANGE]) AS [PrevRange]
    FROM <table name>
)
UPDATE [cte]
SET [STATUS]='Y'
WHERE [RANGE]='001' AND [PrevRange]='000'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Collins SSCrazy Points: 2160 More actions · Answer 5

The alternate solutions don't factor in all of the constraints. Also, both assume the apparent ordinality of dates/ranges will be valid while the OP has offered no assurances that's the case. The OP doesn't reference a "previous" date or a "previous" range. Maybe range '0005' will be inserted or already exists. Also, maybe there are duplicate rows so I'm updating my original solution to add DISTINCT to the cte.

with range_cte(ID) as (
select distinct
  ID
from
  YourTable
where
  [DATE]='2019-09-30'
  and [RANGE]='000')
update yt
set
  yt.[STATUS]='Y'
from
  YourTable yt
 join
  range_cte rc on yt.ID=rc.ID
where
  yt.[RANGE]='001';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

drew.allen SSC Guru Points: 76989 More actions · Answer 6

It's all guesswork until the OP responds. The query I posted was an update to bmg's. I think the best approach is actually to use a MAX/CASE, but I didn't want to write it up until we heard back from the OP.

I still dislike having a hard-coded date in any code.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA