#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171

    Is it possible to INSERT value to the database within INSERT query (if row NULL)?


    Hello Devshed;

    There is a query that returns 1.5 million records from 3 tables table_one, table_two and table_three.
    Code:
    SELECT table_one.id,
           table_two.id,
           table_three.date
    FROM   table_one
           LEFT OUTER JOIN table_two
                        ON table_one.id = table_two.t_one_id
           INNER JOIN table_three
                   ON table_one.id = table_three.t_one_id
    Now as it's a LEFT OUTER JOIN, in some rows that the query returns, table_two may not have a value and obviously shows NULL!

    Would it be possible to INSERT value into table_two if its NULL? The values to be added are the values retrieved by the same query. Perhaps adding something like this to the query:
    Code:
    if('table_two.id'==NULL)
    	{
    	      "INSERT INTO table_two (t_one_id, t_three_date) VALUES (table_one.id, table_three.t_one_id)"	
    	}
    The real case scenario is here. Thank you
    Last edited by zxcvbnm; February 6th, 2013 at 09:12 PM.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    since you can derive the "missing" data from the third table, it would obviously be superfluous to store it in the second table. So that's not what you should do.

    Instead, simply change the query to use the values from table_three as a fallback in case they're NULL in the join. See the COALESCE() function.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by Jacques1
    Hi,

    since you can derive the "missing" data from the third table, it would obviously be superfluous to store it in the second table. So that's not what you should do.
    Hello its been a long time since you helped me how are you champ?
    There is a miss communcation

    The reason I run this query is to make sure there are values in table_two for all the records in table_three. In other words, insert the missing values to table_two. Please look at this (may take 3 seconds to load) and scroll down where you see the yellow text. Those are the values I need to insert into allotments_new table (table_two).

    Cheers man

IMN logo majestic logo threadwatch logo seochat tools logo