-6

I have these rows in SQL Server:

screenshot from management studio

How can I swap the data circled in red?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nayj Arugay
  • 7
  • 1
  • 3
  • 2
    Please consider reading up on [how to ask a good question](https://stackoverflow.com/help/how-to-ask) – Andy Mar 11 '19 at 15:01
  • 1
    Possible duplicate of [Swap values for two rows in the same table in SQL Server](https://stackoverflow.com/questions/2758415/swap-values-for-two-rows-in-the-same-table-in-sql-server) – Ryan Sparks Mar 11 '19 at 15:02
  • thank you for editing @Joel Coehoorn – Nayj Arugay Mar 11 '19 at 15:02
  • It's still bad. Screenshots of sample data, results, code, etc, are NOT OKAY. – Joel Coehoorn Mar 11 '19 at 15:03
  • 2
    Also, please show what you've tried already, and the result you got. – Brian Mar 11 '19 at 15:09
  • What do you even mean by "swap"? As in the values for Room 9103 should appear for 9104, and 9103 should show `NULL`? Is that only in your `SELECT`, or are you looking to `UPDATE` the value of the rows? What have *you* tried so far, and why didn't work? – Thom A Mar 11 '19 at 15:10

1 Answers1

1

Put the original values in a temporary table with the IDs swapped then join to the temporary table whilst updating the table, example code below:

--drop temp table if exists
IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL
    DROP TABLE #Temp

--need to store the original values
SELECT
    *,CASE WHEN Room_Number=9104 then 9103 ELSE 9104 END AS New_Room_Number
    INTO #Temp
    FROM YourTable
    WHERE Room_Number in (9103,9104)

--swap values
UPDATE y
    SET Check_IN=t.Check_IN
        ,Check_OUT=t.Check_OUT
        ,FullName=t.FullName
        ,ContactNumber=t.ContactNumber
        ,Amount=t.Amount
    FROM YourTable        y
        INNER JOIN #Temp  t ON y.Room_Number =t.New_Room_Number
    WHERE y.Room_Number in (9103,9104)
Ryan Sparks
  • 1,347
  • 14
  • 16
  • 1
    You need to alias that new column differently. Resulting temp table would have two Room_Number columns – UnhandledExcepSean Mar 11 '19 at 15:12
  • I received, Msg 207, Level 16, State 1, Line 5 Invalid column name 'ID'. – Nayj Arugay Mar 11 '19 at 15:21
  • 1
    `ID` is probably mean to be `Room_Number`, @NayjArugay. One of the reasons why images are bad; we don't have to transcribe your data if post it as **formatted** `text`. – Thom A Mar 11 '19 at 15:24
  • I changed it to Room_Number, I still received error at the bottom line. Msg 207, Level 16, State 1, Line 16 Invalid column name 'New_Room_Number'. – Nayj Arugay Mar 11 '19 at 15:27
  • @Larnu, this is just my inability to copy and paste, I've edited my answer – Ryan Sparks Mar 11 '19 at 15:27
  • I am still receiving an error. Msg 207, Level 16, State 1, Line 16 Invalid column name 'New_Room_Number'. Msg 209, Level 16, State 1, Line 17 Ambiguous column name 'Room_Number'. Msg 209, Level 16, State 1, Line 17 Ambiguous column name 'Room_Number'. – Nayj Arugay Mar 11 '19 at 15:30
  • @NayjArugay, I've edited to solve for the ambiguous column name, you might need to start a new query or drop the temp table to resolve the other error – Ryan Sparks Mar 11 '19 at 15:33
  • @Ryan Sparks thank you for your help but I still received an error. Msg 207, Level 16, State 1, Line 16 Invalid column name 'New_Room_Number'. – Nayj Arugay Mar 11 '19 at 15:38
  • @NayjArugay your comments imply you don't understand the answer here. Understanding is really important, as it's you who has to support the SQL, not anyone here (including Ryan), If you don't understand you should be asking about it, not asking Ryan to debug the (simple) things. – Thom A Mar 11 '19 at 15:39
  • @Larnu whilst I agree, I probably should have tested my answer to begin with, which I've now done with the test data: CREATE TABLE YourTable (Room_Number NVARCHAR(MAX), Check_IN NVARCHAR(MAX), Check_OUT NVARCHAR(MAX), FullName NVARCHAR(MAX), ContactNumber NVARCHAR(MAX), Amount NVARCHAR(MAX)) insert yourtable values (9104, null, null, null, null, null), (9103, 'a', 'a', 'a', 'a', 'a') – Ryan Sparks Mar 11 '19 at 15:49
  • Thank you! it works now. I've searched for my error. Thank you. – Nayj Arugay Mar 11 '19 at 15:53
  • There's nothing wrong with you answer @Ryan Sparks. Thank you. – Nayj Arugay Mar 11 '19 at 15:55
  • I've appreciated your help guys. – Nayj Arugay Mar 11 '19 at 15:55
  • @Larnu I'm new to SQL. It just happened that I need to learn it to store my project in Java. – Nayj Arugay Mar 11 '19 at 16:00
  • Being new is fine, @NayjArugay , but when getting an answer to a question on Stack Overflow it's important that you understand it, as it's you that has to support it in the end. If you don't understand it, that's a problem as it means you end up having to ask questions again to do more of the work. You learn by asking how something works, or taking the time understand how something does, not by having the work done for you. – Thom A Mar 11 '19 at 16:05