Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Vanilla 2.6 is here! It includes security fixes and requires PHP 7.0. We have therefore ALSO released Vanilla 2.5.2 with security patches if you are still on PHP 5.6 to give you additional time to upgrade.

A few Sql questions - anyone care to help!

peregrineperegrine MVP
edited August 2014 in Development

Is there a way in vanilla 2.1

to set a Column to Autoincrement

->Column('SampleID', 'int(11)', False, 'key','auto_increment')

or

->Column('SampleID', 'int(11)', False, 'key', TRUE)

with item after key being autoincrement.

it doesn't seem so.

if not how does an ALTER in vanilla jargon.

Also how does one do a TRUNCATE in vanilla jargon

I'd like to fill SampleTable with entries based on whether the User Table for a specific column and row equals a certain value.

e.g. if a column in User Table is ABC It has a value of Yes or No.

if it is Yes I would like to grab the Userid and put it into TableB.

So table B  columns

sampleID   UserID   XYX

sampleid is primary key and autoincrements
UserID is grabbed from User Table (if column ABC in User Table is equal to Yes)
XYZ (just defaults to default value)

this works as a SQL command is there a vanilla syntax for insert into.

INSERT INTO TableB ( UserID)
SELECT UserID
FROM GDN_User
WHERE ABC = "Yes"

the problem with insert into though is it will make duplicate UserIDs if run more than once.

so ideally I'd like to join and update if it exists or insert row if it doesn't exist.

I tried this as well

update  TableB b
inner  User u  on
     on u.ABC = "Yes"
set b.UserID = u.UserID

it doesn't seem to be correct syntax and if the record doesn't exist it doesn't insert new record.

I can probably manually get around this, but if someone has insights that would be cool.

I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Comments

  • LincLinc Director of Development Detroit Vanilla Staff

    Auto-increment is only set on a primary key, so you'd just use: ->PrimaryKey('WhateverID')

    Truncate is just a method on the SQL object you can call like Insert or any other. Just pass the tablename.

    You alter a table by redefining it with Structure.

    There isn't a native function in Vanilla for on duplicate key update so you'd need to write a SQL query and pass it directly with Query to do that part.

    peregrinehgtonightUnderDog
  • businessdadbusinessdad Stealth contributor MVP
    edited August 2014

    I haven't checked with 2.1, but with 2.0 you might be able to use Gdn_DatabaseStructure::Columns().

    $Structure->Column('SampleID', 'int(11)', False, 'key');
    $Column = $Structure->Columns('SampleID');
    $Column->AutoIncrement = true;
    

    For the TRUNCATE, you can see Gdn_SQLDriver::Truncate().

    If your target is to insert missing rows and ignore existing ones, you have several different options.

    1. Cleanup and re-insert (the one you are trying now). Might become heavy if it's done frequently.

    2. Insert with JOIN, to only get new rows

    INSERT INTO YourTable
     (Field1, Field2, Field3...) 
    SELECT
      U.Field1
      ,U.Field2
      ,U.Field3
    FROM
      Gdn_User U
      LEFT JOIN
      YourTable YT ON
        (YT.UserID = U.UserID)
    WHERE
      (YT.UserID IS NULL)
    

    3. Insert with IGNORE clause

    INSERT IGNORE INTO YourTable
     (Field1, Field2, Field3...) 
    SELECT
      U.Field1
      ,U.Field2
      ,U.Field3
    FROM
      Gdn_User U
    

    4. Insert missing rows, update existing ones

    INSERT INTO YourTable
     (Field1, Field2, Field3...) 
    SELECT
      U.Field1
      ,U.Field2
      ,U.Field3
    FROM
      Gdn_User U
    ON DUPLICATE KEY UPDATE
      Field1 = <somevalue>
      ,Field2 = <somevalue>
      ,Field3 = <somevalue>
     ...
    
    peregrinehgtonightvrijvlinderUnderDog
  • peregrineperegrine MVP
    edited August 2014

    thank you both Linc and BusinessDad for the quick responses. :)

    @linc said: Auto-increment is only set on a primary key, so you'd just use: ->PrimaryKey('WhateverID')

    Excellent. problem solved for Autoincrement.

    I'll look into the no duplicates and see how that tests out.

    @Businessdad - linc's solution worked for autoincrement so I just used that.

    thx for the Truncate tip.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Sign In or Register to comment.