Update one table using the data from another table

Question

I am dealing with 2 tables.

Users:

+----+----------+-------------------------+
| id | user_id  | datetime                |
+----+----------+-------------------------+
| 1  | 95678367 | 2015-07-03 02:02:29.863 |
| 2  | 72876424 | 2015-07-07 01:04:14.436 |
| 3  | 74293582 | 2015-07-11 10:02:45.523 |
+----+----------+-------------------------+

UserActivation:

+-----+----------+-------------------------+
| id  | user_id  | datetime                |
+-----+----------+-------------------------+
| 1   | 95678367 | 2015-07-03 02:02:29.863 |
| 2   | 09235892 | 2015-07-03 02:02:29.863 |
| 3   | 90328574 | 2015-07-03 02:02:29.863 |
| 4   | 24714287 | 2015-07-03 02:02:29.863 |
| 5   | 02743723 | 2015-07-03 02:02:29.863 |
| 6   | 72876424 | 2015-07-07 01:04:14.436 |
| 7   | 09385732 | 2015-07-07 01:04:14.436 |
| 8   | 74576234 | 2015-07-07 01:04:14.436 |
| 9   | 75439273 | 2015-07-07 01:04:14.436 |
| 10  | 74293582 | 2015-07-11 10:02:45.523 |
| 11  | 94562872 | 2015-07-11 10:02:45.523 |
| 12  | 80367456 | 2015-07-11 10:02:45.523 |
| 13  | 76537924 | 2015-07-11 10:02:45.523 |
+-----+----------+-------------------------+

I am using SQL server 2012. I want to update the timings of table UserActivation. What is going on here that first code insert data in Users table when a user registers. After sometime he activates his account, that data is saving in UserActivation. UserActivation contains numerous columns, but I am showing only those which I am using. The problem is we added datetime column afterwards and till that time hundreds of data is there. What I am trying to do is to update the datetime of UserActivation like follows:

In User table, user_id: 95678367 is first. second is 72876424. I want to update datetime of UserActivation table of rows having id 1 to 5, because id 6 contains the user_id 72876424. So I want to update datetime of rows 1 to 5 in such a way that they comes in 3 seconds increment of datetime from User table.

User table first row has user_id 95678367 and datetime 2015-07-03 02:02:29.863, so update datetime of rows 1-5 of UserActivation(till second user_id of User encounters) as

row1 -> **2015-07-03 02:02:31.863** 
row2 -> **2015-07-03 02:02:34.863**
row3 -> **2015-07-03 02:02:37.863**
row4 -> **2015-07-03 02:02:40.863**
row5 -> **2015-07-03 02:02:43.863**

After that if we strikes second id from Users table. take that datetime from Users table 2015-07-07 01:04:14.436

And start update datetime of UserActivation table with increments of three for rows 6-9 as 10th row cotains 3rd user_id of Users table.

Note: I am trying to write a script so that can I loop through both tables and check one by one user_id of both table and update accordingly, but I am not expert in sql server scripts. Showing how to loop through a SELECT result and update in loop will also help.

Can you explain your data structure? why do you have a user_id column in UserActivation if it's not a foreign key for Users? — Amit
– Amit, Commented Aug 16, 2015 at 11:29
is there any field in both the tables based on which you can order the rows? — Sateesh Pagolu
– Sateesh Pagolu, Commented Aug 16, 2015 at 11:30
@Amit user_id of both table is a foreign key of one another table, that is not mentioned here. — sk786
– sk786, Commented Aug 16, 2015 at 11:30
@DarkKnight ... by id only... they are already ordered...whatever I posted.. — sk786
– sk786, Commented Aug 16, 2015 at 11:32
@sumit - if the user_id column IS a foreign key, why are you updating UserActivation in the rows (id = 2 -> 4) when their user_id isn't matching??? — Amit
– Amit, Commented Aug 16, 2015 at 11:35

Amit · Accepted Answer · 2015-08-16 12:18:26Z

1

One way to calculate the "rolling" timestamp is to use a cross apply. See this:

WITH cte AS (
SELECT ua.id, ua.[datetime], CASE WHEN u.id IS NULL THEN -1 ELSE 0 END AS gapN
FROM UserActivation ua LEFT JOIN Users u
  ON ua.user_id = u.user_id
)
SELECT co.id, co.datetime, DATEADD(second, 3*(co.id-cap.maxID), co.[datetime]) newDatetime
FROM cte co CROSS APPLY (
  SELECT MAX(id) maxID
  FROM cte ca
  WHERE ca.id <= co.id AND ca.gapN = 0
  ) cap

You can see that "live" on SQLFiddle

In order to UPDATE the table, you need to replace the SELECT clause with:

/* SAME AS ABOVE UNTIL THE SELECT LINE */
UPDATE co
SET co.datetime = DATEADD(second, 3 * (co.id - cap.maxID), co.[datetime])
FROM UserActivation co CROSS APPLY (
/* SAME AS ABOVE AFTER THE FROM LINE */

edited Aug 16, 2015 at 12:18

answered Aug 16, 2015 at 12:04

Amit

46.4k9 gold badges83 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sk786 Over a year ago

Thanks for reply... I inserted UPDATE co SET co.datetime = DATEADD(second, 3 * (co.id - cap.maxID), co.[datetime]) In place of SELECT co.id, co.datetime, DATEADD(second, 3*(co.id-cap.maxID), co.[datetime]) newDatetime... Nothing happened...

Amit Over a year ago

@sumit, sorry, see correction. need to use a FROM that uses the actual table, not the CTE.

sk786 Over a year ago

Great it is working in fiddle... but when I am running on my machine its throwing error... Incorrect syntax near 'cte'.

Amit Over a year ago

I can't tell you much about that. try adding a semi-colon before the "WITH".

Sateesh Pagolu · Accepted Answer · 2015-08-16 12:15:29Z

0

Try this

update ua
set ua.datetime = u.datetime 
from users u left join useractivation ua on u.user_id = ua.user_id

;with cte
as
(
    select id,user_id,datetime from useractivation where id=1
    union all
    select ua.id,ua.user_id,
    case when ua.datetime is null then DATEADD(ss,3,c.datetime) else ua.datetime end as datetime
    from cte c inner join useractivation ua on c.id+1 = ua.id
)

update ua
set ua.datetime = c.datetime
from cte c inner join useractivation ua on c.id = ua.id

answered Aug 16, 2015 at 12:15

Sateesh Pagolu

9,6563 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

Update one table using the data from another table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related