is this a case for REPLACE INTO? 1

[thedaver]

I need to make some mass updates to an existing squirrelmail solution.

Table:
describe userprefs;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| user | varchar(128) | NO | PRI | | |
| prefkey | varchar(64) | NO | PRI | | |
| prefval | blob | NO | | | |
+---------+--------------+------+-----+---------+-------+

example values

+-------------------------+-------------------+---------+
| user | prefkey | prefval |
+-------------------------+-------------------+---------+
| dave@example.com | show_html_default | 1 |
| laura@example.com | show_html_default | 0 |
+---------+--------------+------+-----+---------+-------+

========================
I need to set prefval=1 where prefkey="show_html_default"

Difficulty: not every user appears in the "user" column, but I have external list of all users that I need to populate

So I need to manage three cases:
1) no match on "user" + "prefkey" (either user or prefkey could be missing from table) then I need to insert the record with the desired prefval
2) match on "user" + "prefkey" but prefval=0, then I need to set prefval to "1"
3) where the user exists with the desired prefval, I can leave that value alone.

If this a suitable use for a "replace into" statement?

can I do (pseudocode)
(for each user as thisuser)
replace into userprefs(user,prefkey,prefvalue) values ("$thisuser","show_html_default","1")

I just want to make sure I'll hit all three cases.
Thanks!




D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[DonQuichote]

You can do that, but all users will then have a prefvalue of 1.is the intention, go ahead. If there are also other non-zero prefvalues, the following query may be more appropriate:

INSERT INTO userprefs(user,prefkey,prefvalue)
 VALUES (@thisuser,'show_html_default','1')
 ON DUPLICATE KEY UPDATE prefvalue=IF(VALUES(prefvalue)=0,1,VALUES(prefvalue));

Note that double quotes are identifier quotes in ANSI SQL or MySQL in ANSI mode. You can use more than one row with this mechanism if you want.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[thedaver]

Thanks DonQ, that'll take me a bit to think through...
Yes, all users are to end up with prefval=1
Thanks!

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[DonQuichote]

Well it was late last night, so I'll give more explanation now. The @thisuser is the variable. If you use PHP for instance, this is the variable $thisuser that you want to pass. Also the if statement is not really correct, as it contains numbers instead of strings. What it really does is try to insert the setting with a value of one, and when that fails (because of a uniqueness violation), it updates the existing row. I just saw I made a mistake by using VALUES(prefvalue) instead of prefvalue itself. VALUES(prefvalue) is the "new" value that could not be inserted, but I really needed the existing value. So here is my new query and apology for the mistake:

INSERT INTO userprefs(user,prefkey,prefvalue)
       VALUES (@thisuser,'show_html_default','1')
       ON DUPLICATE KEY UPDATE prefvalue=IF(prefvalue='0','1',prefvalue);

Does this work for you?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[thedaver]

followup, the latest code snippet works quite well, many thanks DonQ!

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/