/build/static/layout/Breadcrumb_cap_w.png

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

0 Comments   [ + ] Show comments

Answers (3)

Posted by: GillySpy 13 years ago
7th Degree Black Belt
0
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.
Posted by: stubox 13 years ago
Blue Belt
0
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;
Posted by: GillySpy 13 years ago
7th Degree Black Belt
0
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.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ