CODEWITHSUNDEEP
databasesqlite
XTRUST.ORG
XTRUST.ORG

Reputation: 3392

Sqlite - Update table rows based on another data in the row

I'm looking for the way how to update the empty values, based on the similar value from another row:

Pic1

As you can see from the image: rowid with a number (331) contains the website value (tbe.com), but two another rows marked as '?'. I want to sync the data if some of the rows marked '?' for equal company names.

Thanks!

Upvotes: 0

Views: 462

Answers (2)

forpas
forpas

Reputation: 164089

If actually there are NULLs in the column website instead of ? then

UPDATE tablename AS t1
SET website = (SELECT MAX(t2.website) FROM tablename t2 WHERE t2.company = t1.company)
WHERE t1.website IS NULL

If there are ?:

UPDATE tablename AS t1
SET website = COALESCE((
  SELECT MAX(t2.website) 
  FROM tablename t2 
  WHERE t2.company = t1.company AND t2.website <> t1.website
), t1.website)
WHERE t1.website = '?'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

You could try:

UPDATE yourTable t1
SET website = (SELECT MAX(CASE WHEN t2.website <> '?'
                               THEN t2.website END)
               FROM yourTable t2
               WHERE t2.rowid = t1.rowid);

Upvotes: 1

Related Questions