SQL UPDATE using SELECT

Question

I have a table t1:

date        |     timestamp
                  2015-02-01 00:00:00
                  2015-02-01 00:00:00
                  2015-02-02 00:00:00
                  2015:02:03 00:00:00

I am trying to update the value in date, of just the date from timestamp.

This is what I am using:

update t1 set date = (select date(timestamp) from t1);

but it sets all values to the same as the 1st row?

Answer 1

You can use from statement in following manner:

--MS SQL Server

Update t1 Set t1.date = date(t2.timestamp) from t1 as t2 where t1.Condintionfield=t2.ConditionField;

--SQLITE

Update t1 Set t1.date = (select date(t2.timestamp) from t1 as t2 where t2.conditionfield = t1.conditionfield);

Hope this helps you.

Answer 2

Remove the select:

update t1 set date =  date(timestamp);