SQL issue

[zeezack]

What is wrong with this sql statement?

 

INSERT INTO FEE_TYPES (FEE_TYPE_CODE,UPDATED_BY,UPDATED_DATE,CREATED_BY,CREATED_DATE,TUITION_TYPE_FEE,FES_SHORT_DESCRIPTION,FES_LONG,FES_VAT_CODE,FES_ACCOUNT_CODE,FES_TRANSACTION_TYPE,FES_ISR,LATE_FEE,REFERRAL_FEE,PRIORITY) VALUES('C NOTE', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Credit Note', 'Credit Note', '', '', 'R', 'N', 'N', 'N', '0'),('CASH', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Cash payment', 'Cash payment', '', '', 'R', 'N', 'N', 'N', '0'),('CC', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Credit Card', 'Credit Card Payment', '', '', 'R', 'N', 'N', 'N', '0'), ON DUPLICATE KEY UPDATE FEE_TYPE_CODE=VALUES(FEE_TYPE_CODE), CREATED_DATE=VALUES(CREATED_DATE)

[craygo]

what is the error you are getting

 

[Caesar]

Would make it more enticing to help if you pasted the error that query is yielding for you. Would save a little time.

[zeezack]

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE FEE_TYPE_CODE=VALUES(FEE_TYPE_CODE), CREATED_DATE=VALUES' at line 1

 

 

trying to translate this perl script

 

# $sql_ins  = qq~INSERT INTO $my_tbl ($flds_mysql) VALUES~; #  ($plcs)
#             $sql_dupe = qq~ ON DUPLICATE KEY UPDATE ~;
#             my @f = @flds_mysql; # local copy
#             for ( 0 .. $#idx_key ) { splice(@f,($idx_key[$_]-$_),1); } # remove idx cols
#             # assemble UPDATE clause
#             if ( $no_upd_blnks == 0 ) {
#                 my @t  = ('=VALUES(',')');
#                 $sql_dupe .= join(',',map{$f[$_].$t[0].$f[$_].$t[1]} 0 .. $#f );
#             } else {
#                 my @t = ('=IF(VALUES(','),VALUES(','),','.',')');
#                 $sql_dupe .= join(',',map{$f[$_].$t[0].$f[$_].$t[1]  
#                 .$f[$_].$t[2].$my_tbl.$t[3].$f[$_].$t[4]} 0 .. $#f );
#             }
#             # prepare sth for INSERTing each full stack of records
#             $my1_sql  = $sql_ins;
#             $my1_sql .= join( ',',map{"($plcs)"} 0 .. $rec_bunch ); # 1 more
#             unless ( scalar @f > 0 ) { # least some flds beyond idx flds
#                 if ( scalar @idx_key > 0 ) {
#                     # consider using all idx flds
#                     $sql_dupe .= qq~ $flds_mysql[0]=VALUES($flds_mysql[0])~;
#                 } else { # no flds at all?
#                     $sql_dupe = qq~~;
#                 }
#             }
#             $my1_sql .= $sql_dupe;
#             eval { $my1_sth[$rec_bunch+1] = $my_dbh->prepare($my1_sql); };
#             # set check SQL stmt length vars
#             $sql_ins_len  = length($sql_ins);
#             $plcs_len     = length("($plcs)");
#             $sql_dupe_len = length($sql_dupe);
#             $sql_len      = $sql_ins_len + $sql_dupe_len;
#            
#            
#             $sql_std = (length($sql_ins) + length($sql_dupe));
#             $sql_stub_length = (length($sql_ins) + length($sql_dupe));
#             $sql_length = length($my1_sql);
#  
#  

[craygo]

you need to remove the comma after the last values

 

INSERT INTO FEE_TYPES (FEE_TYPE_CODE,UPDATED_BY,UPDATED_DATE,CREATED_BY,CREATED_DATE,TUITION_TYPE_FEE,FES_SHORT_DESCRIPTION,FES_LONG,FES_VAT_CODE,FES_ACCOUNT_CODE,FES_TRANSACTION_TYPE,FES_ISR,LATE_FEE,REFERRAL_FEE,PRIORITY) VALUES('C NOTE', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Credit Note', 'Credit Note', '', '', 'R', 'N', 'N', 'N', '0'),('CASH', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Cash payment', 'Cash payment', '', '', 'R', 'N', 'N', 'N', '0'),('CC', 'MYRA', '2004-05-28', 'MYRA', '2004-04-01', 'U', 'Credit Card', 'Credit Card Payment', '', '', 'R', 'N', 'N', 'N', '0') 
ON DUPLICATE KEY UPDATE FEE_TYPE_CODE=VALUES(FEE_TYPE_CODE), CREATED_DATE=VALUES(CREATED_DATE)

 

Ray

[zeezack]

INSERT INTO EVENTS (EVENT_STATUS,EVENT_NUMBER,LONG_DESCRIPTION,SHORT_DESCRIPTION,EVENT_TYPE,EXTENDED_EVENT,EVENT_START_DATE,EVENT_START_TIME,EVENT_START,EVENT_END_DATE,EVENT_END_TIME,EVENT_END,DURATION,USER_1,USER_2,USER_3,USER_4,USER_5,USER_6,NOTES_ID,SESSION_CODE,WEEK_PATTERN,SLOTID,RELATED_EVENT,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) VALUES('', '41', 'Hairdressing and English - Stage 3', 'Hairdressing', '', '', '2004-08-01', '09:00:00', '2004-08-01 09:00:00', '2005-07-31', '12:00:00', '2005-07-31 12:00:00', '180', '', '', '', '', '', '', '0', '04/05', '10000000111101111111000111110111100111111111111111101', '0', '0', 'FES', '2004-06-30', '', '0000-00-00'),('', '61', 'Hairdressing and English - Stage 3', 'Hairdressing', '', '', '2004-09-06', '09:00:00', '2004-09-06 09:00:00', '2005-06-20', '10:00:00', '2005-06-20 10:00:00', '60', '', '', '', '', '', '', '0', '04/05', '00000011111110111111100111111011110011111111111100000', '0', '0', 'FES', '2004-07-09', '', '0000-00-00'),('', '101', 'A Level Biology Intensive', 'Int Biology', '', '', '2004-09-21', '00:00:00', '2004-09-21 00:00:00', '2005-05-24', '00:00:00', '2005-05-24 00:00:00', '0', '', '', '', '', '', '', '0', '04/05', '00000000111110111111100111111011110011111111000000000', '0', '0', 'KASTURS', '2004-07-28', '', '0000-00-00'), ON DUPLICATE KEY UPDATE EVENT_NUMBER=VALUES(EVENT_NUMBER) LONG_DESCRIPTION=VALUES(LONG_DESCRIPTION) EVENT_TYPE=VALUES(EVENT_TYPE) EXTENDED_EVENT=VALUES(EXTENDED_EVENT) EVENT_START_DATE=VALUES(EVENT_START_DATE) EVENT_START_TIME=VALUES(EVENT_START_TIME) EVENT_START=VALUES(EVENT_START) EVENT_END_DATE=VALUES(EVENT_END_DATE) EVENT_END_TIME=VALUES(EVENT_END_TIME) EVENT_END=VALUES(EVENT_END) SESSION_CODE=VALUES(SESSION_CODE) CREATED_DATE=VALUES(CREATED_DATE)

 

still get the error

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE EVENT_NUMBER=VALUES(EVENT_NUMBER) LONG_DESCRIPTION=VALUE' at line 1

[craygo]

Look like you still have the comma there to me.

 

remove the comma before ON DUPLICATE KEY

 

Ray