/build/static/layout/Breadcrumb_cap_w.png

Help with SQL code

So, I have a Custom Ticket Rule that takes data that is in Ticket fields and writes them to custom fields in the USER database (The user is decided by 1 of the fields in the ticket)


All of this works perfectly...


What I want to try to do.. is instead of using 1 of the custom fields in the USER, I would like to use a NON custom field "WORK_PHONE" instead...


Here is what  the code looks like that writes to the USER custom fields from the ticket... and below that is the code I tried for the WORK_PHONE field it said it updated 1 record etc.. but I could not find what was updated... :(


ORIGINAL WORKING CODE TO WRITE TICKET FIELD DATA TO CUSTOM FILEDS IN USER RECORD:

insert into USER_FIELD_VALUE (FIELD_ID, USER_ID, FIELD_VALUE)

select  5 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE39 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Direct Phone Number

union all select  3 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE3  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Phone Extension

union all select  8 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE4  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK User ID

union all select  9 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE6  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK Yooz ID

union all select 10 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE8  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK CASHIER ID

union all select 11 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE9  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CRM ID (VinSolutions\Promax)

union all select 13 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE11 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Garber App ID

union all select 14 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE40 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RW/Facebook App User ID

union all select 15 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE13 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Decision Lender (TCI) ID

union all select 16 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE15 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RouteOne ID

union all select 17 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE17 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Dealer Track ID

union all select 18 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE19 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # GM Global Connect ID

union all select 19 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE21 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Honda ID

union all select 20 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE23 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Porsche ID

union all select 21 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE25 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Audi ID

union all select 22 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE27 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Acura ID

union all select 23 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE29 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Volvo ID

union all select 24 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE31 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Shaw Retail ID

union all select 25 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE33 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Imetrik ID

union all select 26 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE37 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RDN ID

union all select 27 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE35 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # ACI ID

on duplicate key update FIELD_VALUE = values(FIELD_VALUE)



This is what I tried:

insert into USER (WORK_PHONE)

select HD_TICKET.CUSTOM_FIELD_VALUE3  WORK_PHONE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Phone Extension



I think I am missing something that ties the USER.WORK_PHONE to the correct user ID


Any suggestions would be appreciated


thanks


Jason


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: jct134 9 months ago
Purple Belt
0

I looked at my code again, and thought this might be the fix:


insert into USER (ID, WORK_PHONE)

select u.ID ID, HD_TICKET.CUSTOM_FIELD_VALUE3  WORK_PHONE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Phone Extension


and get this error:

12/06/2021 15:41:00> Starting: 12/06/2021 15:41:00 12/06/2021 15:41:00> Executing Select Query... 12/06/2021 15:41:00> selected 1 rows 12/06/2021 15:41:00> Executing Update Query... 12/06/2021 15:41:00> mysqli error: [1062: Duplicate entry '6504' for key 'PRIMARY'] in EXECUTE("insert into USER (ID, WORK_PHONE) select u.ID ID, HD_TICKET.CUSTOM_FIELD_VALUE3 WORK_PHONE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = 75800 # Phone Extension") 12/06/2021 15:41:00> Ending: 12/06/2021 15:41:00

Posted by: jct134 9 months ago
Purple Belt
0

Top Answer

I ended up copying the CTR and setting the update SQL to:


UPDATE USER,HD_TICKET

SET USER.WORK_PHONE = HD_TICKET.CUSTOM_FIELD_VALUE3

WHERE (USER.USER_NAME = HD_TICKET.CUSTOM_FIELD_VALUE0) and

 HD_TICKET.ID = <TICKET_IDS>


That seems to be the solution.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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