Update row with select on same table
For MS SQL you can use a join to do this, it might not work with other databases though:
UPDATE t1
SET t1.text = t2.text
FROM table1 t1
INNER JOIN table1 t2 ON t1.id = t2.id
WHERE t1.TEXT IS NULL AND t2.LANG = 'EN'
Sample SQL Fiddle
UPDATE TABLE
SET TEXT = (SELECT TOP 1 text WHERE id = table.id AND LANG ='EN' AND TEXT IS NOT NULL) --TOP 1 INCASE YOU HAVE DUPLICATES
WHERE TEXT IS NULL
You don't specify the database. The following is standard SQL:
UPDATE t
SET TEXT = (SELECT text
FROM t t2
WHERE t.id = t2.id AND LANG ='EN' AND
TEXT IS NOT NULL
)
WHERE TEXT IS NULL;
In the event of duplicates, the following should work:
UPDATE t
SET TEXT = (SELECT max(text)
FROM t t2
WHERE t.id = t2.id AND LANG ='EN' AND
TEXT IS NOT NULL
)
WHERE TEXT IS NULL;
EDIT:
Of course, not all databases support all ANSI standard functionality. In MySQL, you would use a join
instead:
UPDATE t JOIN
(SELECT id, max(text) as text_en
FROM t t2
WHERE LANG ='EN' AND TEXT IS NOT NULL
) ten
ON t.id = ten.id
SET t.TEXT = ten.text_en
WHERE t.TEXT IS NULL;