Update Table Values from Another Table With the Same User Name

Update table values from another table with the same user name

As long as you have suitable indexes in place this should work alright:

UPDATE table_a
SET
      column_a_1 = (SELECT table_b.column_b_1 
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
    , column_a_2 = (SELECT table_b.column_b_2
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
WHERE
    EXISTS (
        SELECT *
        FROM table_b
        WHERE table_b.user_name = table_a.user_name
    )

UPDATE in sqlite3 did not support a FROM clause for a long time, which made this a little more work than in other RDBMS. UPDATE FROM was implemented in SQLite 3.33 however (2020-08-14) as mentioned at: https://stackoverflow.com/a/63079219/895245

If performance is not satisfactory, another option might be to build up new rows for table_a using a select and join with table_a into a temporary table. Then delete the data from table_a and repopulate from the temporary.

SQL: Update column (username) with content from another column in same table (email address)

Your attempt would be correct.

UPDATE WordpressUsers
SET username = emailaddress;

However there could be some 'gotchas' when you try this.

You'll need to ensure the emailaddress can fit within the size of the username column.

-- Verify length to avoid truncation during UPDATE
SELECT MAX(LENGTH(emailaddress)) FROM WordpressUsers;

Then ALTER your table to allow for the increased data length.

ALTER TABLE WordpressUsers
MODIFY username VARCHAR(200);

Example:

DROP TABLE IF EXISTS WordpressUsers;

CREATE TABLE WordpressUsers (
    username VARCHAR(50) NOT NULL,
    emailaddress VARCHAR(200) NOT NULL);


INSERT INTO WordpressUsers (username, emailaddress)
VALUES ('joesmith', 'joe.smith@test.com')
    , ('janedoe', 'jane.doe@test.com')
    , ('johndoe', 'john.doe@test.com')
    , ('someone', 'someone@test.com')
    , ('someone', 'ReallyLongLongEmailAddress@ReallyReallyLongDomain.com');

-- verify        
SELECT * FROM WordpressUsers;

-- Verify length to avoid truncation during UPDATE
SELECT MAX(LENGTH(emailaddress)) FROM WordpressUsers;

ALTER TABLE WordpressUsers
MODIFY username VARCHAR(200);

-- UPDATE
UPDATE WordpressUsers
SET username = emailaddress;


-- verify        
SELECT * FROM WordpressUsers;

[DEMO]

Select User id from one table and update in another table

You can use Triggers for that purpose.

This would update for a specific user if the status changed from 0 to 1

DELIMITER $$

CREATE TRIGGER after_tbl_img_status_update
AFTER UPDATE
ON tbl_img_status  FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status AND NEW.status = 1 THEN
        UPDATE tbl_users  SET total_post  = total_post +1 WHERE id = NEW.user_id;
    END IF;
END$$

DELIMITER ;

IF you don't want to change the Database, you can use a INNER JOIN with And update both table as one.

Your php will then look like:

$results = mysqli_query($mysqli,"UPDATE tbl_users tu INNER JOIN  tbl_img_status  tis ON tu.id = tis.user_id SET tis.post_time=NOW(), tis.status=1, tu.total_post = tu.total_post+1 WHERE tis.status=0 AND tis.scd_time<NOW();");

Updating one table Using data from another table in SQL

update users 
set totalScore = totalScore + p.score
from users left join photo p on users.userId = p.userId
where p.photoID = 5

mysql update column with value from another table

In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

---

Related Topics