StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLoop thru an array and insert into or update a column based on empty values in the column - MySQL
text
Body
copied!<p>I will have user input content(email addresses) that I'll be introducing into a database(MySQL) based on what project those emails are from. I have 1 email table for all of the projects, which aren't that many(about 20), each project will have a separate column. Some emails are going to be removed eventually and some fields in some rows will be marked as empty or null.</p> <p>Most of the time the emails will come in bulk(so to speak) so I'll have to loop thru an array of emails and for every value, introduce that value into whatever project that email belongs to. So far so good, that's already done.</p> <p>However, as I said, some emails are going to be removed. I want to replace empty fields with email addresses and if no empty fields are found insert the emails into the column. </p> <p>Emails will come in bulks so I'll loop thru the array and for every value proceed to execute an insert or update statement depending on the empty fields.</p> <p>Long story short I have this code:</p> <pre><code>SELECT column_name FROM table_name CASE WHEN column_name='' OR column_name IS NULL THEN UPDATE SET column_name='new_value' WHERE column_name='' OR column_name IS NULL ELSE INSERT INTO table_name(column_name) VALUES ('new_value') END </code></pre> <p>Which doesn't work. I'm 100% sure I messed up the syntax somewhere, however the MySQL manual is not much of a help. Some pages suggest using CASE, some suggest using IF.</p> <p>If somebody could take a look at it and give me some pointers, I'd be very grateful.</p> <p>I'm using PHP for this.</p> <p>If anything is unclear in my explanation(I have a habit of doing that), please let me know.</p> <hr> <p>Looking at the IF/ELSEIF from MySQL manual:</p> <pre><code>IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF </code></pre> <p>My new and no so improved code should look like this:</p> <pre><code>SELECT column_name FROM table_name IF column_name='' OR column_name IS NULL THEN UPDATE SET column_name='new_value' WHERE column_name='' OR column_name IS NULL ELSE INSERT INTO table_name (column_name) VALUES ('new_value) END IF </code></pre> <p>Which of course does not work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload