Sql script replace and rearrange numbers

Question

Post reply

Sql script replace and rearrange numbers

diegodeveloper

SSC Enthusiast

Points: 178
More actions
December 21, 2024 at 3:41 am
Go to Answer
#4502679

Hi everyone.
I have this table and this information. (left side of the image)
I need to replace Black with White and rearrange the numbers of the priorities. After the replacement, the numbers should be rearranged. (right side of the image)
CREATE TABLE [dbo].[Priorities](
[Airplane] [nvarchar](50) NULL,
[Priority] [int] NULL,
[Color] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Priorities]([Airplane],[Priority],[Color]) VALUES ('Boeing',1,'Red'), ('Boeing',2,'Black'), ('Boeing',3,'Blue'), ('Boeing',4,'White'), ('Boeing',5,'Yellow')
INSERT INTO [dbo].[Priorities]([Airplane],[Priority],[Color]) VALUES ('Embraer',1,'Red'), ('Embraer',2,'White'), ('Embraer',3,'Blue'), ('Embraer',4,'Black'), ('Embraer',5,'Yellow')
INSERT INTO [dbo].[Priorities]([Airplane],[Priority],[Color]) VALUES ('Cessna',1,'Black'), ('Cessna',2,'White')
INSERT INTO [dbo].[Priorities]([Airplane],[Priority],[Color]) VALUES ('Northrop',1,'Black')
INSERT INTO [dbo].[Priorities]([Airplane],[Priority],[Color]) VALUES ('United',1,'Black'), ('United',2,'White'), ('United',3,'Blue'), ('United',4,'Yellow'), ('United',5,'Green'), ('United',6,'Red')
Could anybody help me with how to achieve this rearrangement?
I really appreciate any help you can provide.

Phil Parkin

SSC Guru

Points: 246985

December 21, 2024 at 5:12 am

Answer

#4502716

Sure. This method creates a temp table containing the desired results & then modifies the main table via a DELETE followed by an INSERT.

If there are millions of rows in your main table, this is likely to be quite a slow process.

DROP TABLE IF EXISTS #Priority;
 
CREATE TABLE #Priority (Airplane NVARCHAR(50) NULL, Priority INT NULL, Color NVARCHAR(50) NULL);
 
INSERT INTO #Priority (Airplane, Priority, Color)
VALUES
('Boeing', 1, 'Red')
,('Boeing', 2, 'Black')
,('Boeing', 3, 'Blue')
,('Boeing', 4, 'White')
,('Boeing', 5, 'Yellow')
,('Embraer', 1, 'Red')
,('Embraer', 2, 'White')
,('Embraer', 3, 'Blue')
,('Embraer', 4, 'Black')
,('Embraer', 5, 'Yellow')
,('Cessna', 1, 'Black')
,('Cessna', 2, 'White')
,('Northrop', 1, 'Black')
,('United', 1, 'Black')
,('United', 2, 'White')
,('United', 3, 'Blue')
,('United', 4, 'Yellow')
,('United', 5, 'Green')
,('United', 6, 'Red');
 
DROP TABLE IF EXISTS #PriorityNew;
 
WITH replaced
AS
(SELECT
      p.Airplane
  ,   Priority = p.Priority
  ,   ColorResult = REPLACE (p.Color, 'Black', 'White')
  FROM #Priority p)
SELECT
    r.Airplane
,   PriorityResult = ROW_NUMBER () OVER (PARTITION BY r.Airplane ORDER BY MIN (r.Priority))
,   r.ColorResult
INTO #PriorityNew
FROM replaced r
GROUP BY r.Airplane
,        r.ColorResult;
 
--Remove any rows in main table which do not exist in results
DELETE trg
FROM #Priority trg
WHERE NOT EXISTS
(
    SELECT 1
    FROM #PriorityNew src
    WHERE src.Airplane = trg.Airplane
          AND src.PriorityResult = trg.Priority
          AND src.ColorResult = trg.Color
);
 
-- Now insert missing rows to main table
INSERT #Priority (Airplane, Priority, Color)
SELECT
    pn.Airplane
,   pn.PriorityResult
,   pn.ColorResult
FROM #PriorityNew pn
WHERE NOT EXISTS
(
    SELECT 1
    FROM #Priority p
    WHERE p.Airplane = pn.Airplane
          AND p.Priority = pn.PriorityResult
          AND p.Color = pn.ColorResult
);
 
SELECT *
FROM #Priority p
ORDER BY p.Airplane
,        p.Priority;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

This gives you close to what you have asked for (but using a temp table, for convenience):

DROP TABLE IF EXISTS #Priority;
 
CREATE TABLE #Priority (Airplane NVARCHAR(50) NULL, Priority INT NULL, Color NVARCHAR(50) NULL);
 
INSERT #Priority (Airplane, Priority, Color)
VALUES
('Boeing', 1, 'Red')
,('Boeing', 2, 'Black')
,('Boeing', 3, 'Blue')
,('Boeing', 4, 'White')
,('Boeing', 5, 'Yellow')
,('Embraer', 1, 'Red')
,('Embraer', 2, 'White')
,('Embraer', 3, 'Blue')
,('Embraer', 4, 'Black')
,('Embraer', 5, 'Yellow')
,('Cessna', 1, 'Black')
,('Cessna', 2, 'White')
,('Northrop', 1, 'Black')
,('United', 1, 'Black')
,('United', 2, 'White')
,('United', 3, 'Blue')
,('United', 4, 'Yellow')
,('United', 5, 'Green')
,('United', 6, 'Red');
 
WITH replaced
AS
(SELECT
      p.Airplane
  ,   Priority = p.Priority
  ,   ColorResult = REPLACE (p.Color, 'Black', 'White')
  FROM #Priority p)
SELECT
    r.Airplane
,   PriorityResult = ROW_NUMBER () OVER (PARTITION BY r.Airplane ORDER BY MIN (r.Priority))
,   r.ColorResult
FROM replaced r
GROUP BY r.Airplane
,        r.ColorResult
ORDER BY r.Airplane
,        PriorityResult;

This reply was modified 4 hours, 42 minutes ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

diegodeveloper SSC Enthusiast Points: 178 More actions · Answer 2

Hi Phil.

Great 🙂

Thank you. It is exactly what I need. But could you help me to persist the data? I mean not select but insert, update and get the data persisted?

diegodeveloper SSC Enthusiast Points: 178 More actions · Answer 3

Great master, thank you very much :).

I have been dealing with it a long time. You did it in a very short time. Impressive

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

diegodeveloper wrote:

Great master, thank you very much :).
I have been dealing with it a long time. You did it in a very short time. Impressive

Thank you. While I may have solved this quite quickly, I have been writing SQL a very long time!

And now I am waiting for others on this forum to post even better solutions.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel