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'