CODEWITHSUNDEEP
mysqlsql
user3709682
user3709682

Reputation:

Update Table with DISTINCT SELECTed values from same 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?

I have tried this

UPDATE sc_product_phrase
SET attr_id = (
    SELECT DISTINCT
        sc_product_phrase.caption,
        sc_product_phrase.design_phrase_id
    FROM
        `sc_product_phrase` as x
    GROUP BY
        sc_product_phrase.caption
    )

but this show error

[Err] 1093 - You can't specify target table 'sc_product_phrase' for update in FROM clause

EDITED

I want my TABLE to look like the following http://sqlfiddle.com/#!2/d65eb/1

NOTE:- I dont want to use that query in that fiddle

Upvotes: 0

Views: 2807

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Your SQL Fiddle makes the question much clearer. You want the minimum attribute id on all rows with the same value in the last column. You can do this with an update/join like this:

UPDATE table1 JOIN
       (SELECT `name_en-GB`, min(Attr_Id) as minai
        from table1
        GROUP BY `name_en-GB`
       ) tt
       on table1.`name_en-GB` = tt.`name_en-GB`
   SET attr_id = tt.minai
 WHERE table1.`name_en-GB` IN ('Bride Name', 'Child Grade') AND
       table1.attr_id <> tt.minai;

I'm not sure if you need the in part. You can update all of them by removing that clause.

Upvotes: 1

Related Questions