Page 1 of 1
sql issue
Posted: Wed Apr 09, 2008 10:47 am
by zeezack
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)
what is wrong with the above code?
Re: sql issue
Posted: Wed Apr 09, 2008 10:51 am
by aceconcepts
It's too long - hahaha
What error do you get when you run it?
Re: sql issue
Posted: Wed Apr 09, 2008 10:54 am
by 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
yeah it gets longer then that. lol
Re: sql issue
Posted: Wed Apr 09, 2008 10:55 am
by zeezack
Trying to translate this perl script..
Code: Select all
$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);
lol any attempts
Re: sql issue
Posted: Wed Apr 09, 2008 11:21 am
by EverLearning
zeezack wrote: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)
what is wrong with the above code?
I think you should remove the comma(marked red above) between end parenthesis and 'ON DUPLICATE KEY UPDATE'