A few Sql questions - anyone care to help!

peregrine · August 2014

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.

Linc · August 2014

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.

businessdad · 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>
 ...

peregrine · 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.

A few Sql questions - anyone care to help!

Comments

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

3. Insert with IGNORE clause

4. Insert missing rows, update existing ones