CODEWITHSUNDEEP
mysql
larpo
larpo

Reputation: 1163

Mysql: Insert row with default new data for each user

Grateful for help with a tricky (for me) query. I am very much a mysql beginner.

The table (v3_community_fields_values) has the following columns:

I have been scratching my head how to write a query that inserts a row for each user where: a) no such row already exists, and b) another user row exists with [field_id] of 45 (45 = user profile type) and the value of either 5 or 6

the new row should contain: [id],[user_id],'75','foobar'

Very grateful for your help.

Upvotes: 1

Views: 1195

Answers (2)

Bohemian
Bohemian

Reputation: 425043

This does it (tested):

insert into v3_community_fields_values
    (user_id, field_id, value)
select user_id, 75, 'foo'
from v3_community_fields_values
where field_id = 45
and value in ('5', '6')
and user_id not in (
select user_id
from v3_community_fields_values
where field_id = 75)

See live demo on SQLFiddle.

Upvotes: 1

Shockley
Shockley

Reputation: 307

I don't know how to implement using one sql statement, but this store procedure may help:

delimiter //
drop procedure if exists insert_something//
create procedure insert_something ()
begin
    DECLARE v_user_id int default 0;
    DECLARE stop int default 0;
    DECLARE cursor1 CURSOR FOR select DISTINCT user_id from v3_community_fields_values where field_id = 45;    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
    OPEN cursor1;
    FETCH cursor1 INTO v_user_id;
    while stop <> 1 do
        insert into v3_community_fields_values (user_id, field_id, `value`) values (v_user_id, '75', 'foobar');
        FETCH cursor1 INTO v_user_id;
    end while;
    CLOSE cursor1;
end //
call insert_something()//

Upvotes: 0

Related Questions