Updating multiple columns in a single record

Dave · 07-04-2008, 12:51 AM

I have a table with about 400 fields, but there is a series of fields entitled n1, n2...n50. IOW, there are 50 "consequtively named" fields.

I need to replace all 50 of these fields with a NULL value in one record.

What is the most efficient way to do this? I understand the SQL UPDATE statement, but was kinda hoping that there is a looping technique that I could use, somehow...(?)

Thanks for any help...

Dave

Kalle · 07-04-2008, 02:28 AM

Is there any unique identifier with each row? eg. a primary key, unique key ect.?

Dave · 07-04-2008, 03:10 AM

Hi, Kalle --

Yes, there is a unique identifying number for each row in the table.

Dave

Kalle · 07-04-2008, 12:36 PM

Hey

As far as I know theres no command to "updte all the rows" like you can select all rows in a statement.

So you need to do something like:

PHP Code:


			
$sql = 'UPDATE `table` SET `n1`, `n2`, ..., `n50` = NULL WHERE `id` = \'' . $something_unique . '\' LIMIT 1;';

Dave · 07-04-2008, 12:49 PM

Thanks, Kalle --

Just for me to be specific, in the UPDATE query I would have to literally type the name of each of the 50 columns (n1, n2, n3, etc.) in order to set them all to NULL.

Is that correct?

Again, thank you...

Dave

Kalle · 07-04-2008, 01:11 PM

Yea thats what it looks like, you can ofcourse generate it in PHP if its names like "n[n]" where [n] is a number like:

PHP Code:


			
function generate_update_rows($value = NULL)

{

    $compiled_stack = Array();



    /* Start number: 1 - End number 50 */

    $times = 50;



    for($n = 1; $n < ($times + 1); ++$n)

    {

        $compiled_stack[$n] = '`n' . $n . '`' . (!is_null($value) ? ' = \'' . addslashes($value) . '\'' : '') . (($times - 1) != $n ? ', ', '');

    }



    return(implode(' ', $compiled_stack));

}

And in your query just have like:

PHP Code:


			
$sql = 'UPDATE `table` SET ' . generate_update_rows('NULL') . ' LIMIT 1;';

Dave · 07-04-2008, 05:25 PM

Wow, Kalle, thank you so much!

Yes, that is what I was looking for. Now it will take me some time to absorb what you have written, though I do get the gist of it right away.

I'm sure I'll have a question or two as I attempt to implement your code.

Dave

Kalle · 07-04-2008, 05:27 PM

I just updated my code, there was an error with comma's :)

Dave · 07-04-2008, 06:57 PM

I think it is bad luck to read your code a 2nd time!

Thanks again,
Dave

Dr John · 07-12-2008, 06:22 PM

Quote:

Originally Posted by Dave

I have a table with about 400 fields, but there is a series of fields entitled n1, n2...n50. IOW, there are 50 "consequtively named" fields.

Sounds like you have an unnormalised database...

Without seeing the field list and all the business logic, it's hard to normalise things for you of course, but if you have a number of columns called n1, n2, n3, etc, I'd guess that you should be normalising these into another table, with the main table's unique id as a foreign key in the new table.

One of the classic examples of unnormalised data that can lead to multiple columns like this is a person and their children
this is the wrong way to do it.
person (id, name, childname1, childname2, childname3, childname4, childname5, childname6....childnameN}

the correct way is like this
person {id, name,}
child (personID, childname)

variations on this:-
person (id, name)
teleNo (personID, tnumber)

business (id, name)
store (businessID, location)

movie (id, name)
actor (movieID, name)

and so on.

So, revisit your database design now, before you get too far into this problem, as it sounds like the design is wrong.