SQL UPDATE statement

Business Central/NAV Technical Forum
1

I have a little issue. It’s not regarding NAV but I hope that here is one or two SQL experts who can help me, as I’m not a shark to SQL statements.

As you might have seen in the member listing, then you migth often see members, who if you check their old posts have posts who are “older” than their date of registration. This comes from the sad fact that some of these dates was lost at our conversion from Snitz.

So to make the dates a bit better, then the only thing I can do is to make a search on the old posts, and update the createdate if the postdate of the oldest post in older.

I have two tables:
cs_Users (fields: UserID, CreateDate)
cs_Posts (fields: UserID, PostDate)

So it should be very simple. I want to make an update statement which will update CreateDate, if there are any PostDates which are older than the CreateDate.

Who can help?

2

This statement should do the trick

UPDATE cs_Users
SET createDate = cs_Posts.PostDate
FROM cs_Posts INNER JOIN
cs_Users ON cs_Posts.UserID = cs_Users.UserID AND cs_Posts.PostDate < cs_Users.createDate

3

This query doesn’t guarantee that updated created date will the first post. It can be any post before created date.

4

This should do the trick.

UPDATE cs_Users
SET CreateDate = p.FirstPostDate
FROM cs_Users u
JOIN (SELECT UserID,
FirstPostdate = Min(PostDate)
FROM cs_Posts
GROUP BY UserID) AS p ON u.UserID = p.UserID
where p.FirstPostDate < u.CreateDate

You have to create a virtual (not materialixed) table (“table” p above) with user-id and the first posting date for each user. Then use that “table” in a join to do the update.

/Lars

5

This did it! Thanks!

6

I see. no info was given on PK of the tables. I thought UserID was the PK.

7

I changed my statement without looking at the solution above. [8-|]

UPDATE cs_Users
SET createDate = cs_PostsUnique.PostDate
FROM
(Select UserID, Min(PostDate) as PostDate
FROM cs_Posts
Group by UserID
) as cs_PostsUnique
INNER JOIN
cs_Users ON cs_PostsUnique.UserID = cs_Users.UserID AND cs_PostsUnique.PostDate < cs_Users.createDate

I don’t write sql statement that often, but they are fun when I write them. It makes you think differently than when you write boring Navision code.

I guess writing in one language long enough and you take it for granted.

I can’t wait for one Product Dynamics with a different programming language to learn.

8

And thanks to you also!

My own SQL statement skils are not something I have spend too much time on, but somethimes I wish I had done it. And yes it’s rather fun…