A little MySql help

[giorgino]

Hi all.

I'm migrating a vB4 board and I have to insert a default value in a custom user field that I created in the new xenforo installation.

Let me explain.

We've the user_field_value table with the three field user_id, field_id and field_value.
I need to populate this table with a default value for each user for field_id='campo' and field_value='dato'.
All the same value for all users, nothing else.

I imagine that a INSERT INTO function should do the job, but unfortunately I'm a MySql newbe.
Can someone help me or give me some directions on this?

Thank you

 

[tafreehm]

Hi all.

I'm migrating a vB4 board and I have to insert a default value in a custom user field that I created in the new xenforo installation.

Let me explain.

We've the user_field_value table with the three field user_id, field_id and field_value.
I need to populate this table with a default value for each user for field_id='campo' and field_value='dato'.
All the same value for all users, nothing else.

I imagine that a INSERT INTO function should do the job, but unfortunately I'm a MySql newbe.
Can someone help me or give me some directions on this?

Thank you

INSERT will add new row. What you need is UPDATE

it might be something like this:

UPDATE user_field_value
 
SET field_id = 'campo', field_value = 'dato';

try above at your own risk..

 

[karll]

Something like this:

insert into xf_user_field_value (user_id, field_id, field_value)
      select user_id, 'campo', 'dato' from xf_user;

Edit: The above assumes the rows don't already exist in user_field_value. If they do, you need to use "update" like tafreehm suggested.

Edit 2: Sorry, there were a couple of mistakes in the query. I've modified it slightly, hopefully better now.

 

[giorgino]

try above at your own risk..

I've a test DB Thank you for advice.

INSERT will add new row. What you need is UPDATE

Edit: The above assumes the rows don't already exist in user_field_value. If they do, you need to use "update" like tafreehm suggested.

Sure, the rows doesn't exist I need to create one new row in user_field_value for each user in xf_user

insert into user_field_value (user_id, field_id, field_value)
select user_id, field_id='campo', field_value='dato' from user;
phpMyAdmin return:
#1146 - Table 'mydb.user_field_value' doesn't exist
but the table exist

 

[tafreehm]

I've a test DB Thank you for advice.


Sure, the rows doesn't exist I need to create one new row in user_field_value for each user in xf_user

insert into user_field_value (user_id, field_id, field_value)
select user_id, field_id='campo', field_value='dato' from user;
phpMyAdmin return:
#1146 - Table 'mydb.user_field_value' doesn't exist
but the table exist

you probably have a prefix. And sorry, I misunderstood, I thought you were trying to update the existence data.

 

[giorgino]

you probably have a prefix

Yes, I'm an idiot! thank you!

And sorry, I misunderstood

Thank you for suggestions

Updating query with db prefix return this
#1054 - Unknown column 'field_id' in 'field list'

 

[karll]

Yes, I'm an idiot! thank you!

Thank you for suggestions

Updating query with db prefix return this
#1054 - Unknown column 'field_id' in 'field list'

I made a couple of mistakes, sorry. I've edited the query in my previous post, feel free to try again ...

 

[giorgino]

Work great! Thank you so much!