How can I swap the values of two rows for a particular column? (

ghz 8months ago ⋅ 61 views

How can I swap the values of two rows for a particular column? (When the primary key for the rows are the same)

I have a table called tval and when I run this SQL query:

select * from tval where id = 402280209;

I get:

    id      fid               ts                        val
402280209   3765    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000
402280209   3771    2021-09-20 00:00:00.000   2023-09-20 00:00:00.000
402280209   3782    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000

I am trying to swap the values of val for fid = 3771 and 3782.

So in the example above, I would like this as the output:

    id      fid               ts                          val
402280209   3765    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000
402280209   3771    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000
402280209   3782    2021-09-20 00:00:00.000   2023-09-20 00:00:00.000

Answers

To swap the values of val for fid = 3771 and fid = 3782 for a specific id, you can use conditional logic in an update statement. Here's how you can achieve this in SQL:

UPDATE tval
SET val = CASE
    WHEN fid = 3771 THEN (
        SELECT val FROM tval
        WHERE id = 402280209 AND fid = 3782
    )
    WHEN fid = 3782 THEN (
        SELECT val FROM tval
        WHERE id = 402280209 AND fid = 3771
    )
    ELSE val
END
WHERE id = 402280209 AND fid IN (3771, 3782);

In this query:

  • We use a CASE statement to conditionally set the value of val based on the fid.
  • If fid = 3771, we set val to the value of val where fid = 3782 for the same id.
  • If fid = 3782, we set val to the value of val where fid = 3771 for the same id.
  • For all other fid values, we leave val unchanged.
  • We restrict the update to only the rows where id = 402280209 and fid is either 3771 or 3782.

Make sure to replace 402280209 with the actual id you want to update. Additionally, ensure that the subqueries inside the CASE statements return a single value to avoid errors.

#sql