Can someone check a simple query real quick?

[aebstract]

INSERT INTO CUSTOMFIELDVIEW (CUSTOMFIELDVIEW.CFID, CUSTOMFIELDVIEW.CFNAME, CUSTOMFIELDVIEW.CFDESCRIPTION, CUSTOMFIELDVIEW.CFSORTORDER, CUSTOMFIELDVIEW.CFTABLEID, CUSTOMFIELDVIEW.CFTYPEID, CUSTOMFIELDVIEW.CFREQUIRED, CUSTOMFIELDVIEW.RECORDID, CUSTOMFIELDVIEW.INFO)
(SELECT 5, CUSTOMFIELDVIEW.CFNAME, CUSTOMFIELDVIEW.CFDESCRIPTION, 1, 97022306, CUSTOMFIELDVIEW.CFTYPEID, CUSTOMFIELDVIEW.CFREQUIRED, CUSTOMFIELDVIEW.RECORDID, CUSTOMFIELDVIEW.INFO
        FROM CUSTOMFIELDVIEW 
        WHERE CUSTOMFIELDVIEW.CFID = 3)

 

I just want to make sure this is gonna do what I want it to do. There are 9 columns in the customfieldview table, I need to make a copy of every one of them that has a cfid of 3, changing 3 column's information and keeping the rest the same. The three changes that need to be made are the cfid to 5, cfsortorder to 1 and tableid to 97022306. I want it to do this for every row that has a cfid of 3.

[Mchl]

You can use aliases in place of full table name. Will make it easier to read.

 

Also a convention is to use small letters for column/table names, and capital letters for SQL words.

 

Other than that, looks fine to me.

 

INSERT INTO CUSTOMFIELDVIEW (CFID, CFNAME, CFDESCRIPTION, CFSORTORDER, CFTABLEID, CFTYPEID, CFREQUIRED, RECORDID, INFO)
SELECT 5, C.CFNAME, C.CFDESCRIPTION, 1, 97022306, C.CFTYPEID, C.CFREQUIRED, C.RECORDID, C.INFO
        FROM CUSTOMFIELDVIEW AS C
        WHERE C.CFID = 3

 

 

[aebstract]

Okay, I think this will work but I have to do it to a different table/columns now. The first column is called ID and its auto increment. Do I put SELECT , c.customfield, c.info, etc? Or what?

 

EDIT: I can just remove the id field completely, and it'll create a new one for every row that is created, right?

[Mchl]

Right

[aebstract]

I just got this error

 

validation error for column ID, value "*** null ***"

 

from this query

 

INSERT INTO CUSTOMSET (CUSTOMFIELDID, INFO, RECORDID)
SELECT 5, C.INFO, C.RECORDID
        FROM CUSTOMSET AS C
        WHERE C.CUSTOMFIELDID = 3

 

So maybe it isn't auto increment? In the case that it isn't, can I easily just use the next available number for the column ID, for each row created? Would that cause that error?

[Mchl]

Just check CREATE TABLE statement for this table. If the column is not AUTO_INCREMENT, perhaps you can make it so? If not, you'll have to read the highest ID in separate query and use it to create new ID in insert query. You should lock table for write in this scenario.

[aebstract]

I don't have access to see how it was created, or if I do I don't know how to get to it. So that leaves me to either finding out from the manufacturer if it is auto increment or make it so it grabs the next available number. I can only run through like 1 query at a time I think, not sure if what you explained will work if it's two completely separate queries?

 

 

edit: gonna try and accomplish what I'm after in a different way. Realized I can skip a huge step.

[Mchl]

http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html