CODEWITHSUNDEEP
mysqlsqlsql-update
Sam
Sam

Reputation: 6552

Update timestamp column in one table using a column in another table

MySQL is just not my forte, it seems. I added a timestamp column to an existing table where the default should be CURRENT_TIMESTAMP. However, all existing rows have a blank timestamps (0000-00-00 00:00:00). What I'd like to do is update these rows' timestamp column (named ts) using another table's timestamp column. I have two columns in both tables that I'd like to match first. So if (tb1.col1 = tb2.col1 AND tb1.col2 = tb2.col2) OR (tb1.col1 = tb2.col2 AND tb1.col2 = tb2.col1), then use tb2's timestamp column as the update value in tb1's timestamp column.

Does that make sense? How would I write an update statement like this?

Thanks for reading. All help is appreciated.

Upvotes: 0

Views: 752

Answers (2)

tadman
tadman

Reputation: 211670

If I'm to go with a literal interpretation of what you're asking for, it'd look something like this:

UPDATE tb1,tb2 SET tb1.ts=tb2.ts
  WHERE (tb1.col1 = tb2.col1 AND tb1.col2 = tb2.col2) OR
    (tb1.col1 = tb2.col2 AND tb1.col2 = tb2.col1)

You may need to fuss around with the conditions there. I hope you have a test database to get this working first.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

Its pretty straight forward see the last sample in the docs

Update tbl1, tbl2
set 
  tb1.ts = tbl2.ts
WHERE (tb1.col1 = tb2.col1 AND tb1.col2 = tb2.col2) 
   OR (tb1.col1 = tb2.col2 AND tb1.col2 = tb2.col1)

Upvotes: 1

Related Questions