CODEWITHSUNDEEP
mysqlsql
P. Danielski
P. Danielski

Reputation: 560

Sql query for searching values in the same table

i've got a problem. I have a table with these columns:

languageID languageItem

Every row can have only "2" or "3" as languageID and languageItem is always different, but only with the same languageID. For example i can have:

2 | header.title
2 | header.description
3 | header.description
3 | header.title

The problem is that now the rows which have the languageID as "3" are less than rows which have the languageID as "2" and i need that for each languageID there must be the same languageItem(s). Like:

2 | header.title
2 | header.description
2 | header.button
3 | header.title
3 | header.description

Is missing header.button for "3"

I want select all rows which the languageID 2 have more and then "copy" them and insert with the languageID 3.

Thanks

EDIT: The rows don't have only these 2 columns, but also others.

Upvotes: 0

Views: 282

Answers (3)

Tobsey
Tobsey

Reputation: 3400

INSERT INTO LanguageItems(languageID, languageItem)
SELECT
    3,
    WantedValues.languageItem
FROM
    LanguageItems WantedValues
    LEFT JOIN LanguageItems ExistingValues ON 
            WantedValues.languageItem = ExistingValues.languageItem 
            AND WantedValues.languageID = 2
            AND ExistingValues.languageID = 3
WHERE
    WantedValues.languageID = 2 
    AND ExistingValues.languageID IS NULL

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You can use INSERT IGNORE here:

insert ignore into mytable(languageId, languageItem)
select 3 as languageId, languageitem from mytable where languageid = 2;

(Provided of course there is a unique index on languageId + languageItem.)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this by just inserting values that don't exist. The query looks like:

insert into table(languageId, languageItem)
    select 3, languageitem
    from table t
    where languageid = 2 and
          not exists (select 1
                      from table t3
                      where t3.languageid = 3 and t3.languageitem = t.langaugeitem
                     );

Upvotes: 1

Related Questions