#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0

    Problem with my SQL code


    Hello,
    I have a problem with this code in the MySQL interpreter


    Code:
    SET @totalUsers = 3;
    INSERT INTO User (email, username, password, firstName) VALUES
    ('mail@mail.com', 'garcia', 12712, 'Grace'),
    ('mai121l@mail.com', 'anton', 128812, 'Anton'),
    ('mai12l@mail.com', 'alex', 172172, 'Alex');
    SET @profileId = LAST_INSERT_ID();
    
    DELIMITER |
    
    BEGIN
    DECLARE count INT;
    
    SET @profileId = (@profileId-1);
    SET count = 0;
    
    WHILE (count < @totalUsers) DO
       INSERT INTO UserPrivacy (ID, privacyProfile, userSearch, saveViews) VALUES ((@profileId+1), 'all', 'yes', 'yes');
       SET count = (count+1);
    END WHILE;
    END;
    
    DELIMITER ;
    Can you tell me the mistake I made in the DELIMITER block, please?

    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    in my opinion, your mistake is having a separate userprivacy table that should be part of the user table

    never manipulate id numbers -- there's always a chance that they aren';t going to be as consecutive as you think they are

    does your user table have a UNIQUE index besides the obvious auto_number primary key? hint: it should

    if you're going to have a separate userprivacy table, it should have a foreign key to the user table UNIQUE column so that you are not dependent upon using three consecutive auto_increment numbers

    then you can use INSERT SELECT by selecting the three UNIQUE column values that you just inserted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo