www.webdeveloper.com
Results 1 to 10 of 10

Thread: unable to insert

  1. #1
    Join Date
    May 2010
    Location
    Manila, Philippines
    Posts
    189

    unable to insert

    can anybody tell me what's wrong with my query? I have zero records yet on the table but then it's always zero rows affected whenever I execute this query. It should insert the record I'm trying to insert since there's no match on it's condition. Thanks for any advise. =)


    Code:
    insert into tblassoc(unit,period,balance,penalty,code,due,payment) 
    select '101-a',now(),2675,0,'ASC','2010-12-18',0.00 from tblassoc 
    where not exists (select unit,period from tblassoc 
    where unit='101-a' and period=date_format(now(),'%Y-%m-%d'))

  2. #2
    Join Date
    Jul 2009
    Location
    Aliso Viejo, CA USA
    Posts
    6

    Learn - master the fundamentals

    I highly recommend that you go back and study fundamental SQL principles.

    The examples have not been tested, but they may work for you.

    Insert a record:

    Code:
    insert into tblassoc(unit,period,balance,penalty,code,due,payment) 
    values ('101-a',now(),2675,0,'ASC','2010-12-18',0.00);
    Select and display any/all records:

    Code:
    select unit,period,balance,penalty,code,due,payment from tblassoc;
    Select and display specific record(s):

    Code:
    select unit,period,balance,penalty,code,due,payment from tblassoc where period>='2010-12-01';
    Take the time to learn about database design and SQL.

    -Richard

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    You might be able to solve your problem with a view.

    CREATE VIEW
    or
    SELECT INTO statement...

    ---------------------
    If you insist on your query for whatever reason; your syntax is messed up I think.
    I believe there's a few requirements for a query of that nature to work:

    1) you need to specify table names in front of the variable names (it's good practice regardless even if you don't need them)
    2) the difference in the number of fields you're setting and selecting should be 0
    3) You need the keyword VALUES and your select query should be inside of parenthesis as if it were a value itself. By putting your select query inside of parenthesis you can stack select queries like so:

    INSERT INTO tableA (columnA) VALUES
    (SELECT tableB.columnB from tableB),
    (SELECT tableC.columnC from tableC),
    (SELECT 1/0),
    (SELECT '101-a');

    best of luck.

    P.S: "I have zero records yet"-- you should try using some sample databases available on the net. You should also format your query in a way that's easy to read and spot bugs, I'm not an SQL parser.

  4. #4
    Join Date
    Jul 2009
    Location
    Aliso Viejo, CA USA
    Posts
    6

    First things first

    Using SELECT INTO or CREATE VIEW seems, frankly, beyond the scope of what any developer who is obviously struggling to create/debug a statement to INSERT one row into a single table.

    I think perhaps developer iahne has probably seen just enough "advanced SQL" to believe that it's normal or required to have a bunch of conditions and formatting all mixed in together - just to insert a record.

    Perhaps developer iahne will tell us in more abstract terms, what he/she is trying to accomplish with the INSERT statement. Are you just trying to insert a row, or something more involved?

  5. #5
    Join Date
    May 2010
    Location
    Manila, Philippines
    Posts
    189
    Good question synapp2. I have 2 tables. What I want to do is just insert my records from table1 to table2 without duplicating it on table2. That's why I'm using the condition "where not exists". But it doesn't allow me to do so if table2 is empty (which usually occurs only at the first time use of the query).

  6. #6
    Join Date
    Mar 2010
    Location
    Singapore
    Posts
    367
    Actually it will also be helpful if the database name is mentioned as some database support proprietary SQL extensions. I notice in this forum SQL it is almost exclusively MySQL.

    Not trying to start a war but sometimes the poster maybe using some other database instead of just MySQL.

  7. #7
    Join Date
    May 2010
    Location
    Manila, Philippines
    Posts
    189
    (-_-)...it's MySQL..
    database name: dbname
    table name/s : table1, table2
    Fields : itemcode,itemdesc (on both tables)

  8. #8
    Join Date
    Sep 2010
    Posts
    160
    Hello,

    if you just run the SELECT statement, what result do you get?

    Archie

  9. #9
    Join Date
    May 2010
    Location
    Manila, Philippines
    Posts
    189
    Actually nothing. That's what I don't understand though. I'm selecting, for example, 6 to be my data which my select statement would be "select 6 from table2". If table2 have at least one record the query would return 6 as its data but if no record on the table there will be no "6" result from that query. You can try it too to check for result.

  10. #10
    Join Date
    Sep 2010
    Posts
    160
    What happens if you just run this part of your select?

    Code:
    select '101-a',now(),2675,0,'ASC','2010-12-18',0.00 from tblassoc
    Achie

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles