Update with Two Tables

Update with two tables?

Your query does not work because you have no FROM clause that specifies the tables you are aliasing via A/B.

Please try using the following:

UPDATE A
    SET A.NAME = B.NAME
FROM TableNameA A, TableNameB B
WHERE A.ID = B.ID

Personally I prefer to use more explicit join syntax for clarity i.e.

UPDATE A
    SET A.NAME = B.NAME
FROM TableNameA A
    INNER JOIN TableName B ON 
        A.ID = B.ID

Updating two tables with an inner join

For MySQL UPDATE with JOIN syntax is different, the SET part should come after the JOIN

Use the following query to update the entries:

UPDATE question q
INNER JOIN answer a ON a.answer_id = q.answer_id
SET q.question = 'dmvvnnv'
    ,a.comment = 'covonfvk'
    ,a.rating = 5
WHERE a.doctor_id = 8

How to join two tables in an UPDATE statement?

You're on the right track, but you need to specify a JOIN between the tables:

UPDATE tweets JOIN users ON tweets.user_id = users.user_id
  SET tweets.spam = 1
WHERE users.name = 'SUSPENDED'

Update multiple tables in SQL Server using INNER JOIN

You can't update more that one table in a single statement, however the error message you get is because of the aliases, you could try this :

BEGIN TRANSACTION

update A
set A.ORG_NAME =  @ORG_NAME
from table1 A inner join table2 B
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID

update B
set B.REF_NAME = @REF_NAME
from table2 B inner join table1 A
    on B.ORG_ID = A.ORG_ID
    and A.ORG_ID = @ORG_ID

COMMIT

Update multiple tables in one query

This should work:

const profile = await db.user.update({
    where: { id: u.id },
    data: {
      firstName: 'name',
      lastName: 'name',
      profile: {
        update: {
          aboutMe: 'aboutMe',
          location: 'aboutMe',
          profession: 'aboutMe',
        },
      },
    },
    include: { profile: true },
  })

Updating two Tables at same time but rollback if update fails

Your best option is to run both updates in a single transaction.

An example, albeit in Java, that is purposefully designed to fail (on the 2nd update) and thus rollback the first successful update.

So first we have an Update Method (function in Kotlin) that utilises and update Dao @Update int update(Bookmark bookmark); that is in the AllDao interface :-

public static void updateTwo(TheDatabase db, Bookmark bookmark1, Bookmark bookmark2) {
    boolean updateOk = true;
    AllDao dao = db.getAllDao();
    SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
    sdb.beginTransaction();
    if (dao.update(bookmark1) > 0) {
        if (dao.update(bookmark2) < 1) {
            Log.d("UPDATETWO","Second Update failed");
            updateOk = false;
        }
    } else {
        Log.d("UPDATETWO","First Update failed to update anything.");
        updateOk = false;
    }
    if (updateOk) {
        Log.d("UPDATETWO","Both updates OK");
        sdb.setTransactionSuccessful(); //<<<<< ONLY SET TRANSACTION OK if both updates (aka ROLLBACK if not both OK)
    }
    sdb.endTransaction();
}

• Note SupportSQliteDatabase used but really runInTransaction should be used (am looking at using this, so I may update answer)
• Logging included for showing results.
• P.S. I have allowed running on main thread for convenience/brevity

And in an Activity I have code that is designed to not update (i.e. the update fails (albeit that it doesn't it just doesn't update which I assume is what you mean by fail rather than an exception being thrown)):-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();
        dao.deleteAll();
        Bookmark b1 = new Bookmark(), b2 = new Bookmark();

        // Insert Row
        b1.setPostTitle("P1");
        b1.setPostUrl("U1");
        b1.setId(dao.insert(b1)); // sets the id so row can be updated

        // Prepare for update
        b1.setPostUrl("URL1"); // change so there is an update to be performed i.e. change U1 to URL1
        // Prepare a second (no-existent so doomed to fail) Row for update
        b2.setPostTitle("P2");
        b2.setPostUrl("U2");
        // Do the update
        Bookmark.updateTwo(db,b1,b2);
        for (Bookmark b: dao.getAll()) {
            Log.d("BOOKMARKINFO","ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
        }
    }
}

Result

The Log :-

D/UPDATETWO: Second Update failed
D/BOOKMARKINFO: ID = 1 PostTitle = P1 PostURL =U1

i.e. the Row is back to U1 not URL1 (aka ROLLED BACK)

If before the update the line b2.setId(dao.insert(b2)); is added (so the 2nd row exists and thus will be updated (even though no data is actually change)) then the Log is :-

D/UPDATETWO: Both updates OK
D/BOOKMARKINFO: ID = 1 PostTitle = P1 PostURL =URL1
D/BOOKMARKINFO: ID = 2 PostTitle = P2 PostURL =U2

i.e. first row is URL1 (aka NOT ROLLED BACK)

• The above as it stands is only suitable for single row updates as it only checks if any update has been made rather than the number of updates matches.

Update

here's the equivalent using runInTransaction. It appears that "The transaction will be marked as successful unless an exception is thrown in the Runnable." is only if an SQLite Exception is thrown. As such a precarious dao was added to force an exception i.e.

@Insert(onConflict = OnConflictStrategy.ABORT)
long abort(Bookmark bookmark);

• Precarious as it assumes that the Bookmark exists and therefore cannot be inserted as the UNIQUE constraint is violated (id column).

The alternative code being :-

public static void update2(TheDatabase db,Bookmark b1, Bookmark b2) {
    try {
        db.runInTransaction(new Runnable() {
            @Override
            public void run() {
                try {
                    upd(db, b1, b2);
                } catch (Exception e) {
                }
            }
        });
    } catch (Exception e) {}
}

private static void upd(TheDatabase db, Bookmark b1, Bookmark b2) throws Exception {
    boolean updateOk = true;
    AllDao dao = db.getAllDao();
    if (dao.update(b1) > 0) {
        if(dao.update(b2) < 1) {
            updateOk = false;
            Log.d("UPDATETWO","Second Update failed");
        }
    } else {
        Log.d("UPDATETWO","First Update failed to update anything.");
    }
    if (!updateOk) {
        dao.abort(b1); // Force SQLite Exception to ROLLBACK
    } else {
        Log.d("UPDATETWO","Both updates OK");
    }
} 

The following code in the activity was used for testing :-

    dao.deleteAll();
    Bookmark b1 = new Bookmark(), b2 = new Bookmark(), b3 = new Bookmark();

    // Insert Row
    b1.setPostTitle("P1");
    b1.setPostUrl("U1");
    b1.setId(dao.insert(b1)); // sets the id so row can be updated

    // Prepare for update
    b1.setPostUrl("URL1"); // change so there is an update to be performed i.e. change U1 to URL1
    // Prepare a second (no-existent so doomed to fail) Row for update
    b2.setPostTitle("P2");
    b2.setPostUrl("U2");
    // Do the update
    Bookmark.updateTwo(db,b1,b2);
    logInfo("-A1");
    b2.setId(dao.insert(b2));
    Bookmark.updateTwo(db,b1,b2);
    logInfo("-A2");

    b1.setPostUrl("U R L 1");
    b3.setPostTitle("P3");
    b3.setPostUrl("U3");
    Bookmark.update2(db,b1,b3);
    logInfo("-B1");
    b3.setId(dao.insert(b3));
    Bookmark.update2(db,b1,b3);
    logInfo("-B2");
}

private void logInfo(String extra) {
    for (Bookmark b: dao.getAll()) {
        Log.d("BMINFO" + extra,"ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
    }
}

This uses both methods -A (as per log) using the SupportSQliteDatabase the -B using runInStransaction. The Log :-

2021-07-01 13:19:50.944 D/UPDATETWO: Second Update failed
2021-07-01 13:19:50.946 D/BMINFO-A1: ID = 1 PostTitle = P1 PostURL =U1
2021-07-01 13:19:50.949 D/UPDATETWO: Both updates OK
2021-07-01 13:19:50.951 D/BMINFO-A2: ID = 1 PostTitle = P1 PostURL =URL1
2021-07-01 13:19:50.951 D/BMINFO-A2: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.953 D/UPDATETWO: Second Update failed
2021-07-01 13:19:50.963 D/BMINFO-B1: ID = 1 PostTitle = P1 PostURL =URL1
2021-07-01 13:19:50.963 D/BMINFO-B1: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.966 D/UPDATETWO: Both updates OK
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 1 PostTitle = P1 PostURL =U R L 1
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 3 PostTitle = P3 PostURL =U3

Edit re the comment:-

Wouldnt't the anotation Transaction safe me the "boilercode"

Tried using :-

@Transaction
public static boolean update3(AllDao dao,Bookmark b1, Bookmark b2) {
    boolean updateOk = true;
    if (dao.update(b1) > 0) {
        if (dao.update(b2) < 1) {
            updateOk = false;
        }
    }
    if (!updateOk) {
        dao.abort(b1);
    }
    return updateOk;
}

However, the abort results in a crash e.g. :-

E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so67958704javaroomconvertexistingdb, PID: 6136
    java.lang.RuntimeException: Unable to start activity ComponentInfo{blah....}: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: mylist_data.ID (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
....

So I believe that @Transaction, unlike runInTransaction, isn't designed for Rollback.

---

Related Topics