sharkyenergy
Reputation: 4183
MS Sql - update X number of rows based on another table's content
I have 2 tables, for this example "table1" and "table2".
table1 contains 100 entries and its structure is like this:
id (int), count(int), code(varchar(50))
table2 contains many thousands of rows and its structure is like this:
id (int), code(varchar(50))
I would need to make a select on table1, select all rows, all the data, then switch to table 2 and copy the code to X number of rows. where X is the number extracted from the table 1.
so if for example table1 contains a line "1","17","abc123" and a line "2","26","cde456" then it should update the first 17 lines of table2 with the code "abc123" and the next 26 lines with "cde456".
can this be done with a query or do I have to do it with a dedicated softare?
Edit:
It is not working properly. this is what I tested so far from the answer below:
WITH Grps AS(
SELECT LAG([qty],1,1) OVER (ORDER BY timecol ASC) AS CountStart,
[qty] AS CountEnd,
sscc,
ROW_NUMBER() OVER (ORDER BY timecol asc) AS RN
FROM QtyInfo),
CTE AS(
SELECT id,
sscc,
ROW_NUMBER() OVER (ORDER BY id asc) AS RN
FROM CrateCodes)
UPDATE C
SET sscc = G.sscc
FROM CTE C
JOIN Grps G ON C.RN BETWEEN G.CountStart AND G.CountEnd where G.RN < 10;
what it does is: on table 2 the update begins always from row 0. so it keeps overwriting the same rows continuously.
here is some sample data:
USE [Line01]
GO
/****** Object: Table [dbo].[Table1] Script Date: 24.11.2020 16:58:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[id] [int] IDENTITY(1,1) NOT NULL,
[count] [int] NULL,
[code] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Table2] Script Date: 24.11.2020 16:58:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table2](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [varchar](50) NULL CONSTRAINT [DF_Table2_code] DEFAULT ('XXX')
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table1] ON
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (1, 2, N'ABC')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (2, 5, N'BCD')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (3, 1, N'DEF')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (4, 2, N'EFG')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (5, 5, N'HIJ')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (6, 3, N'IJK')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (7, 2, N'LMN')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (8, 3, N'OPQ')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (9, 4, N'RST')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (10, 4, N'UVW')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (11, 1, N'XYZ')
GO
INSERT [dbo].[Table1] ([id], [count], [code]) VALUES (12, 2, N'TZU')
GO
SET IDENTITY_INSERT [dbo].[Table1] OFF
GO
SET IDENTITY_INSERT [dbo].[Table2] ON
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (1, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (2, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (3, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (4, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (5, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (6, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (7, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (8, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (9, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (10, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (11, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (12, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (13, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (14, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (15, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (16, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (17, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (18, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (19, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (20, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (21, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (22, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (23, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (24, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (25, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (26, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (27, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (28, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (29, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (30, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (31, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (32, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (33, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (34, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (35, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (36, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (37, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (38, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (39, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (40, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (41, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (42, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (43, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (44, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (45, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (46, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (47, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (48, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (49, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (50, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (51, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (52, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (53, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (54, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (55, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (56, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (57, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (58, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (59, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (60, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (61, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (62, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (63, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (64, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (65, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (66, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (67, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (68, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (69, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (70, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (71, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (72, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (73, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (74, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (75, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (76, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (77, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (78, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (79, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (80, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (81, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (82, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (83, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (84, N'XXX')
GO
INSERT [dbo].[Table2] ([id], [code]) VALUES (85, N'XXX')
GO
SET IDENTITY_INSERT [dbo].[Table2] OFF
GO
Upvotes: 0
Views: 35
Answers (1)
Thom A
Reputation: 96028
This might be what you are after:
CREATE TABLE dbo.Table1 (ID int IDENTITY,
[Count] int,
Code varchar(6));
GO
CREATE TABLE dbo.Table2 (ID int IDENTITY,
Code varchar(6));
GO
INSERT INTO dbo.Table1
VALUES(5,'abc123'),(3,'def456');
GO
INSERT INTO dbo.Table2
VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
GO
WITH Grps AS(
SELECT ISNULL(SUM([Count]) OVER (ORDER BY ID ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),1) AS CountStart,
SUM([Count]) OVER (ORDER BY ID ASC) AS CountEnd,
Code
FROM Table1),
CTE AS(
SELECT ID,
Code,
ROW_NUMBER() OVER (ORDER BY ID) AS RN
FROM Table2)
UPDATE C
SET Code = G.Code
FROM CTE C
JOIN Grps G ON C.RN BETWEEN G.CountStart AND G.CountEnd;
GO
SELECT *
FROM dbo.Table2;
GO
DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;
Upvotes: 1
Related Questions
- Update table based on a count of records in another table
- Updating specific rows to values based on the count of rows in another table
- SQL - Updating rows with individual rows from another table
- UPDATE a selection of rows to a chosen value based on a condition in another table
- Update statement based on another table
- Loop through table records and update from another table records in SQL Server
- SQL Server update a table based on values in the same table
- SQL Server : update rows based other rows in the same table
- update data in table based on data in another table
- Update columns in a row based on the records from the other table