June 17, 2024 at 5:12 am
Good Morning,
how to assign query one results to query 2 student id, each record should have each value, rather assigning one to all 20k students. please advise. i tried one but it is assigned same id to all STG_Student. please
-- Query 1
SELECT STATESTUDENTID
FROM DEV_SLDS.LEGACY.STUDENTIDPOOL
WHERE IDSTATUS = 'UNASSIGNED'
AND DATEINITIALASSIGNED IS NULL
--- This query resylts gives several thousands of studentid numbers that are never assigned to student.
-- Query 2 this STATEIDENTIFICATIONNUMBER need to populate with query 1 each record should have one unique record from query 1
SELECT * FROM STAGE.STG_STUDENT s
SET STATEIDENTIFICATIONNUMBER = Assign from STUDENTIDPOOL.STATESTUDENTID
WHERE COMMENTS = 'NEW ID FROM POOL REQUIRED'
and trim(FILEID) = trim('a0xHv000000fNWqIAM ')
AND STATEIDENTIFICATIONNUMBER IS NULL;
Thank you,
Asit
June 17, 2024 at 9:22 am
I suggest that you push the results of query 1 into a temp table, with an additional column
Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
Then add a ROW_NUMBER() column to query 2 and join on that column back to the identity column. Now you are in a position to do your update.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2024 at 11:44 am
Dear Phil Good Morning,
I have id columns which is autoincrement in the table LEGACY.STUDENTIDPOOL already which is unique value
can you please give statement if possible. Thank you much
June 17, 2024 at 12:15 pm
You want me to write your queries for you, despite not having provided DDL and consumable sample data yourself?
If you understand the principle behind what I have suggested, you should be able to code this yourself.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2024 at 1:17 pm
Hello Phil,
Sorry My mistake. here is code for ddl some dml samples , Can you please help. Thanks
CREATE TABLE STUDENTIDPOOL (
ID INT IDENTITY(1,1) NOT NULL,
STATESTUDENTID NUMERIC(10,0),
IDSTATUS VARCHAR(MAX) DEFAULT 'UNASSIGNED',
DATECREATED DATETIMEOFFSET DEFAULT GETDATE(),
DATEINITIALASSIGNED DATETIMEOFFSET,
CREATEDBY NVARCHAR(50) DEFAULT SUSER_SNAME(),
PRIMARY KEY (ID)
);
INSERT INTO STUDENTIDPOOL (STATESTUDENTID, IDSTATUS, DATECREATED, DATEINITIALASSIGNED, CREATEDBY)
VALUES
(1234567890, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(9876543210, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(5555555555, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(7777777777, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(8888888888, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(9999999999, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(1111111111, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(2222222222, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(3333333333, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
(4444444444, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME());
----- So above query has randon studentid that are unassigned yet to any students.
CREATE TABLE STG_STUDENT (
ID INT IDENTITY(1,1) NOT NULL,
FILEID VARCHAR(40),
INSERTDATETIME DATETIMEOFFSET,
COMMENTS VARCHAR(1000),
STATEIDENTIFICATIONNUMBER NUMERIC(10,0),
FIRSTNAME VARCHAR(40),
MIDDLENAME VARCHAR(40),
LASTNAME VARCHAR(50),
GENDER VARCHAR(40),
DATEOFBIRTH VARCHAR(10),
CITYOFBIRTH VARCHAR(40),
STATEOFBIRTH VARCHAR(40),
COUNTRYOFBIRTH CHAR(6)
);
INSERT INTO STG_STUDENT (FILEID, INSERTDATETIME, COMMENTS, STATEIDENTIFICATIONNUMBER, FIRSTNAME, MIDDLENAME, LASTNAME, GENDER, DATEOFBIRTH, CITYOFBIRTH, STATEOFBIRTH, COUNTRYOFBIRTH)
VALUES
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'John', 'Michael', 'Doe', 'Male', '1990-01-01', 'New York', 'NY', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Jane', NULL, 'Smith', 'Female', '1985-05-15', 'Los Angeles', 'CA', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Michael', 'James', 'Johnson', 'Male', '1982-09-30', 'Chicago', 'IL', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Sarah', NULL, 'Brown', 'Female', '1995-03-20', 'Houston', 'TX', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'William', 'Robert', 'Davis', 'Male', '1993-07-10', 'Phoenix', 'AZ', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Emily', 'Grace', 'Anderson', 'Female', '1988-11-25', 'Philadelphia', 'PA', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Daniel', 'Thomas', 'Wilson', 'Male', '1991-04-05', 'San Diego', 'CA', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Jessica', NULL, 'Martinez', 'Female', '1987-06-12', 'Dallas', 'TX', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Matthew', 'Andrew', 'Taylor', 'Male', '1984-08-18', 'Miami', 'FL', 'USA'),
('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Lauren', 'Elizabeth', 'Clark', 'Female', '1994-02-28', 'Seattle', 'WA', 'USA');
Here we need to assign studentid from pool table to the STG_STUDENT.STATEIDENTIFICATIONNUMBER one id for each one record.
June 17, 2024 at 1:50 pm
I think it's easier to use ROW_NUMBER(), like this:
;WITH cte_STUDENTIDPOOL AS (
SELECT STATESTUDENTID, ROW_NUMBER() OVER (ORDER BY STATESTUDENTID) AS row_num
FROM /*DEV_SLDS.LEGACY.*/STUDENTIDPOOL
WHERE IDSTATUS = 'UNASSIGNED'
AND DATEINITIALASSIGNED IS NULL
),
cte_STG_STUDENT AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS row_num
FROM /*STAGE.*/STG_STUDENT s
--SET STATEIDENTIFICATIONNUMBER = Assign from STUDENTIDPOOL.STATESTUDENTID
WHERE COMMENTS = 'NEW ID FROM POOL REQUIRED'
AND trim(FILEID) = trim('a80sdl20a390aXyski ')
AND STATEIDENTIFICATIONNUMBER IS NULL
)
UPDATE css
SET STATEIDENTIFICATIONNUMBER = cs.STATESTUDENTID /*, DATEINITIALASSIGNED = ...*/
FROM cte_STUDENTIDPOOL cs
INNER JOIN cte_STG_STUDENT css ON css.row_num = cs.row_num
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2024 at 4:29 pm
Thank you Scott. is there any way we can do it with out temp tables,. I am trying this in snowflake so just checking to see if we can do with subquery etc. if possible Thank you much.
June 17, 2024 at 5:28 pm
Scott's solution uses zero temp tables.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2024 at 5:37 pm
I don't know Snowflake SQL, but based on Google for their syntax, derived tables are allowed in an UPDATE:
UPDATE target SET v = b.v /* actual UPDATE example from Snowflake web site */
FROM (SELECT k, MIN(v) v FROM src GROUP BY k) b
WHERE target.k = b.k;
Not clear if the table to be UPDATEd may be derived, but, if so, then maybe this?!:
UPDATE css
SET STATEIDENTIFICATIONNUMBER = cs.STATESTUDENTID /*, DATEINITIALASSIGNED = ...*/FROM (
SELECT STATESTUDENTID, ROW_NUMBER() OVER (ORDER BY STATESTUDENTID) AS row_num
FROM /*DEV_SLDS.LEGACY.*/STUDENTIDPOOL
WHERE IDSTATUS = 'UNASSIGNED'
AND DATEINITIALASSIGNED IS NULL
) AS cs
INNER JOIN (
SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS row_num
FROM /*STAGE.*/STG_STUDENT s
WHERE COMMENTS = 'NEW ID FROM POOL REQUIRED'
AND trim(FILEID) = trim('a80sdl20a390aXyski ')
AND STATEIDENTIFICATIONNUMBER IS NULL
) AS css ON css.row_num = cs.row_num
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply