CODEWITHSUNDEEP
mysqlsql
user3934473
user3934473

Reputation:

Issue with updating a table

I have this table

enter image description here

What I want to do is that Select the attr_id WHERE a DISTINCT name_en-GB appears and then SET that selected attr_id for that name_en-GB

I can do this by writing individual queries but I want to know is there any way I can do this in one query?

Upvotes: 0

Views: 68

Answers (3)

user3934473
user3934473

Reputation:

I have finally done this

   UPDATE sc_product_phrase
    JOIN (
        SELECT
            `caption`,
            min(design_phrase_id) AS minai,
            required
        FROM
            sc_product_phrase WHERE LENGTH(`options`) < 1
        GROUP BY
            `caption`
    ) tt ON sc_product_phrase.`caption` = tt.`caption`
    AND sc_product_phrase.required = tt.required
    SET design_phrase_id = tt.minai
    WHERE
        sc_product_phrase.design_phrase_id <> tt.minai
    AND LENGTH(
        sc_product_phrase.`options`
    ) < 1

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

you can do this simply with an update query and a select.. however I don't know what is the criteria for the first appearence of an attr_id

assumption - first appearance is by the smallest attr_id

UPDATE table t,
(   SELECT MIN(attr_id), `name_en-GB`
    FROM table
    GROUP BY `name_en-GB`
) t1
SET t.attr_id = t1.attr_id
WHERE t1.`name_en-GB` = t.`name_en-GB`

Upvotes: 0

peterm
peterm

Reputation: 92785

You can do

UPDATE table1
   SET attr_id = CASE `name_en-GB` WHEN 'Bride Name' THEN 142
                                   WHEN 'Child Grade' THEN 270 END
 WHERE `name_en-GB` IN('Bride Name', 'Child Grade')

Here is SQLFiddle demo

Upvotes: 1

Related Questions