how to assign studentid from one table to other one? please

  • 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

     

  • 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

  • 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

     

  • 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

  • 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.

  • 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".

  • 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.

  • 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

  • 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