MySQL syntax issues
Hi
I'm trying bulk import some KB articles by importing them via CSV into an asset type I've created, then copying those asset entries into new KB articles using a helpdesk rule.
I'm stuck on the SQL syntax. I've tried using a Select statement in the Select part of the rule to select all from my KB article asset type and then use the update part to insert the values. But I cant reference the values in the Insert statement that the Select found. i.e. the Insert statement doesnt recognise strID when the Select statement says SELECT ID as strID FROM Asset_Data_38
So I've tried to combine the both but get errors about unrecognised fields.
What am I doing wrong?
insert into ADVISORY(ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;
Error:
41:17> Starting: Mon, 24 Jan 2011 12:41:17 +0000
41:17> Executing Select Query...
41:17> selected 2 rows
41:17> Executing Update Query...
41:17> mysql error: [1054: Unknown column 'TITLE' in 'field list'] in EXECUTE("insert into ADVISORY(ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;")
41:17> Ending: Mon, 24 Jan 2011 12:41:17 +0000
I'm trying bulk import some KB articles by importing them via CSV into an asset type I've created, then copying those asset entries into new KB articles using a helpdesk rule.
I'm stuck on the SQL syntax. I've tried using a Select statement in the Select part of the rule to select all from my KB article asset type and then use the update part to insert the values. But I cant reference the values in the Insert statement that the Select found. i.e. the Insert statement doesnt recognise strID when the Select statement says SELECT ID as strID FROM Asset_Data_38
So I've tried to combine the both but get errors about unrecognised fields.
What am I doing wrong?
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;
41:17> Starting: Mon, 24 Jan 2011 12:41:17 +0000
41:17> Executing Select Query...
41:17> selected 2 rows
41:17> Executing Update Query...
41:17> mysql error: [1054: Unknown column 'TITLE' in 'field list'] in EXECUTE("insert into ADVISORY(ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;")
41:17> Ending: Mon, 24 Jan 2011 12:41:17 +0000
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
GillySpy
13 years ago
If you do a select * from ASSET_DATA_38 you will see that there are no such columns as TITLE, NOTE, etc.
ASSET will look something like FIELD_X where X is an integer corresponding to the ID from ASSET_FIELD_DEFINITION. Your query for the asset data will look something like this:
select FIELD_X as TITLE, FIELD_Y as NOTE /*, etc */ from ASSET_DATA_38
Also you do not want to select the ID field. ID in the ADVISORY table is an autoincrement field so just let it increment. If you must specify the numbers then make sure they are unique.
ASSET will look something like FIELD_X where X is an integer corresponding to the ID from ASSET_FIELD_DEFINITION. Your query for the asset data will look something like this:
select FIELD_X as TITLE, FIELD_Y as NOTE /*, etc */ from ASSET_DATA_38
Also you do not want to select the ID field. ID in the ADVISORY table is an autoincrement field so just let it increment. If you must specify the numbers then make sure they are unique.
Posted by:
stubox
13 years ago
Thanks GillySpy, knew I was being stupid somehow! For reference here is the simple helpdesk rule to create knowledgebase articles.
Select statement:
SELECT * FROM ASSET_DATA_38
Update statement:
insert into ADVISORY(TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT FIELD_384,FIELD_385,FIELD_388,FIELD_386,FIELD_388,FIELD_389,FIELD_390,FIELD_391 FROM ASSET_DATA_38;
SELECT * FROM ASSET_DATA_38
Update statement:
insert into ADVISORY(TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT FIELD_384,FIELD_385,FIELD_388,FIELD_386,FIELD_388,FIELD_389,FIELD_390,FIELD_391 FROM ASSET_DATA_38;
Posted by:
GillySpy
13 years ago
Glad you got it working! Wanted to note that this query will only work for you in this ORG of your KBOX. Queries on assets are not re-useable without some modifications because the field numbers will be different on other KBOXes (and ORGs).
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.