'set a column to copy a column from another table (on condition)

What I currently have is:

UPDATE card, records
IF(records.date_returned == null) THEN SET
card.last_seen = records.date_loaned
ELSE SET card.last_seen = records.date_returned
WHERE card.card_no = records.card_no

A little background-- the table records has two columns-- date_loaned and date_returned, with date_returned set as null by default. I was wondering whether its possible to change the last_seen column in temp_card to date_returned when it gets updated

Pretty sure its impossible, but I guess I'm trying my luck!

I was kinda hoping it to be automatic (e.g. when records get updated, this triggers the last_seen to change).

mysqlsql


Solution 1:[1]

You can try like this..

Update  Card A INNER JOIN Record B ON (A.card_no =B.card_no) SET A.last_seen =(
                           Case WHEN B.date_returned==null
                           then B.date_loaned 
                           Else B.date_returned
                           End
                          )

Solution 2:[2]

How about using CASE like

UPDATE card, records
SET
 card= CASE records.date_returned == null 
 THEN  card.last_seen = records.date_loaned
 ELSE SET card.last_seen = records.date_returned
WHERE card.card_no = records.card_no

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions