How do I merge this update code with this code below?

Question

Basically, I'm trying to update a date if this function below it is true.

I'm going to use this code in MS Visual Studio to compare it to the index of a checked item in a checked list box...

UPDATE rent 
SET dat_vrac = CONVERT(smalldatetime, CURRENT_TIMESTAMP)


(SELECT CAST(rownumber AS VARCHAR)
FROM (SELECT dat_vrac, row_number() 
       OVER (order by dat_vrac) AS 'rownumber'
       FROM rent) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE (
  cast (rownumber as varchar)
  ) > '0')


SqlCommand komanda = new SqlCommand("WITH CTE_RENT AS ( SELECT dat_vrac, ROW_NUMBER() OVER (order by dat_vrac) rn FROM rent) UPDATE CTE_RENT SET dat_vrac = CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) WHERE rn ='" + chlb_return.Items.Cast<int>() + "'", konekcija);

What do you intend that logic to do? Not the update statement, but all the row number stuff below that? — Eric Brandt, Dec 23 '19 at 19:18
[How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Eric Brandt, Dec 23 '19 at 19:19
Basically what I'm trying to do afterward is find the rownumber and compare it to a checked list box index. For some reason, I have to convert it to varchar. — Marko Savicevic, Dec 23 '19 at 19:23
`SqlCommand komanda = new SqlCommand("SELECT CAST(rownumber AS VARCHAR) FROM (SELECT dat_vrac, row_number() OVER (order by dat_vrac) AS 'rownumber' FROM rent) TableExpressionsMustHaveAnAliasForDumbReasons WHERE (cast (rownumber as varchar)) > '" + chlb_return.Items.Cast() + "'", konekcija);` — Marko Savicevic, Dec 23 '19 at 19:23
I'm really sorry I'm trying to explain this the best way I can... — Marko Savicevic, Dec 23 '19 at 19:25
Please add more context and merge your comments into your question. explain what you have tried, what you expect and what you are getting now. This will help the community to help you quicker. — timur, Dec 24 '19 at 00:30

LukStorms · Accepted Answer · 2019-12-23T19:33:49.517

1

Since you don't like to use an alias for a sub-query.

And ROW_NUMBER starts with 1, not 0.

So try this:

WITH CTE_RENT AS
(
    SELECT dat_vrac
    , ROW_NUMBER() OVER (ORDER BY dat_vrac) rn
    FROM rent
)
UPDATE CTE_RENT 
SET dat_vrac = CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP)
WHERE rn > 1

But it could be that the ROW_NUMBER needs to be partitioned by something only you can know.

    , ROW_NUMBER() OVER (PARTITION BY house_id ORDER BY dat_vrac) rn

edited Dec 23 '19 at 19:33

answered Dec 23 '19 at 19:28

LukStorms

28,916
5
31
45

Hey LukStorms! Thank you very much for your answer, I appreciate it much more than you think. I didn't seem to explain this fully... I edited the post now. I need this to be varchar because Visual Studio gives me an error if it's not. – Marko Savicevic Dec 23 '19 at 19:38
Some simplyfied fake sample data with expected results often helps to explain your intension. But before trying to update, just select in a tool like SSMS with only a SELECT. So that you get an idea about what you'll be updating. Don't blindly trust SQL. – LukStorms Dec 23 '19 at 19:43
1

Btw, rn is a INT. So no need put single quotes around it in your SqlCommand. Currently I don't get what you're trying to do. Update the date for the N'th record? – LukStorms Dec 23 '19 at 19:51
SqlCommand komanda = new SqlCommand("WITH CTE_RENT AS (SELECT dat_vrac, ROW_NUMBER() OVER (ORDER BY dat_vrac) rn FROM rent) UPDATE CTE_RENT SET dat_vrac = CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) WHERE (cast (rn as varchar)) = '" + 6 + "'", konekcija); – Marko Savicevic Dec 23 '19 at 19:55
See here for example I chaged the checkedListItems to number 6. Like this it's working but right now I'm having trouble changin the checkedListItems to a number... Also tried CheckedIndices – Marko Savicevic Dec 23 '19 at 19:56
Also, read [this answer](https://stackoverflow.com/a/7505842/4003419) and learn to be warry of [Bobby Tables](https://bobby-tables.com/) – LukStorms Dec 23 '19 at 19:56
Not sure how that checkedListItems comes into play. But in the end you're builing a string that's supposed to be a SQL you could run manually to test it regardless of VB or C# code. – LukStorms Dec 23 '19 at 20:08
I'll try to figure that one out by myself. Thank you very much for your help! – Marko Savicevic Dec 23 '19 at 20:13

How do I merge this update code with this code below?

1 Answers1