MySQL – Inserting Rows with WHERE Condition

insertMySQLmysql-5.5select

Structure

1 table named: scan

2 columns named: id, and date

Problem

I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the condition is equal to 0.

Query 1

Show '1' if the latest ID row in the database has scan date of today otherwise show '0' when no today's date is present.

SELECT
  COUNT(scan.date) AS datecheck
FROM scan
WHERE scan.id = (SELECT
    MAX(scan.id) AS datecheck
  FROM scan
  WHERE scan.date = CURDATE())

Query 2

If there is no entry for today, meaning a results of '0' then create a new row with ID+1 and the current date.

INSERT INTO `scan`(`id`, `date`) SELECT (MAX(id)+1), CURDATE() FROM scan;

I have been trying for hours now with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.

Best Answer

Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert:

INSERT INTO scan (id, date)
SELECT (MAX(id)+1), CURDATE()
FROM scan
HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0;

To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)

Related Solutions

Mysql – how to write conditional where clause query

It can be done using some multiple joins from table1 into table2 and then use those joins combined with case/calculation logic, or using sub-selects. But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN 
        CASE         
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable. So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.

Mysql – Processlist show most of the queries are sending data for select query

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload. Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized innodb_buffer_pool_size with InnoDB.

Sending Data was a very poorly-chosen name for the thread state. It simply does not mean that the server is necessarily "sending" anything. The server may, in fact, still be doing table scans and may have nothing, yet, to send anywhere, and the thread will still be in this state. A more appropriate name for the state might have been "preparing data to send."

By contrast, when the server is in fact actually sending data to the client, the state is Writing to net and it's pretty rare to actually see that in the processlist unless the resultset is particularly large or the client is particularly slow about accepting data from the network.

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Writing to net

The server is writing a packet to the network.

^{— http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html}

Best Answer

Related Solutions

Mysql – how to write conditional where clause query

Mysql – Processlist show most of the queries are sending data for select query

Related Question