CODEWITHSUNDEEP
sqlite
Prabha
Prabha

Reputation: 135

SQLite - Update a column based on values from another table's columns

I am trying to update Record1's ID to Record2's ID when:

Record1

| ID | Weight | Name |
|----|--------|------|
|  1 |     10 |    a |
|  2 |     10 |    b |
|  3 |     10 |    c |

Record2

| ID | Weight | Name |
|----|--------|------|
|  4 |     20 |    a |
|  5 |     20 |    b |
|  6 |     20 |    c |

I have tried the following SQLite query:

update record1
set id = 
  (select record2.id 
   from record2,record1 
   where record1.name=record2.name 
   and record1.weight<record2.weight)

Using the above query Record1's ID is updated to 4 for all records.

Upvotes: 1

Views: 2109

Answers (1)

CL.
CL.

Reputation: 180060

Writing SELECT ...record1 introduces a new instance of the record1 table that hides the outer one.

To be able to refer to the current row in the outer query, just remove table1 from the FROM clause:

UPDATE record1
SET id = (SELECT record2.id 
          FROM record2
          WHERE record1.name   = record2.name 
            AND record1.weight < record2.weight);

Upvotes: 4

Related Questions