CODEWITHSUNDEEP
sqlitejoin
lich
lich

Reputation: 290

update sqlite3 table from another one

Question is relevant only to SQLite3. How to update a table row from another table? My example:

Table "cashpoints" has columns "id", "bank_id"
Table "banks_cashpoints" has columns "cp_id", "b_id"
Tables should be joined by condition

cashpoints.id = banks_cashpoints.cp_id

So I need: banks_cashpoints.b_id -> cashpoints.bank_id

Each of my attempts failed. For example this (ERROR: near "from": syntax error):

UPDATE cashpoints
SET bank_id = b_id
    FROM banks_cashpoints bc
WHERE cp_id = id

As I read here, it's not possible to use FROM statement is update query. But I have no idea how to do this without it.

Upvotes: 0

Views: 3137

Answers (1)

LeleDumbo
LeleDumbo

Reputation: 9340

Not sure if it works, but it's worth trying:

UPDATE
  cashpoints
SET
  bank_id = b_id
WHERE
  b_id IN (
    SELECT
      b_id
    FROM
      banks_cashpoints
    WHERE
      cp_id = id
  );

Upvotes: 2

Related Questions