help : Update statement

Here's the statement I need some help with:

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job'
WHERE Username = 'username'
;

what' i'm trying to do is insert the job description to the most recent entry (which is why i sorted) but it's adding the job description everywhere it sees username (which is more than once) so i need it to update the latest line

something like:

UID | Username | Job description
1 username test_job
2 username test_job1
3 username test_job2

when it updates it should add to UID 3 since it's the most recent. if anyone knows how to write the update statement please let me know. THANK YOU!

What determines if a row is 'most recent'?

Is it the larger UID?

What are the other columns in the tables?

|||I'm not sure how to make it so it selects the most recent. I wanted to sort it and select the first one (which would be most recent) but I'm not sure how to select the first one.

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc LIMIT 1
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job100'
WHERE UID = UID
;

that's what i wanted it to do, but instead of
WHERE UID = UID i want it to be something like WHERE UID = "most recent UID" some how

|||

So how do you determine which is the 'most recent UID'?

Is is the largest UID value? (It's still not clear.)

|||alright i'll try to explain myself better... I'm writing a program in vb.net

the table looks something like this:

UID | Username | Job | PLC_option | PLC_software
0 user1 9 option1 software1

that's what I want the data to look like. When the program runs, it adds a new UID and the users username to the table... another form opens and the user selects a job, i want the job to be added to that same column (where '9' is) but what's happening is the username dissapears. Another form will come up and the user selects the PLC option

what i'm trying to accomplish is the ID and username gets inserted and then the job and plc options get updated into that same row...

hope that's a little more clear.. thank you
|||

Kenny,

There are at least a couple of ways to go on this. First, let me ask a couple more questions.

What is the code that inserts the new UID and UserName into the table?

How is that code executed?

|||that's where i'm not sure... right now i have this code...

this is in my main form where the UID and username gets sent to the table...

strsuccess = " INSERT INTO sanbox.timespent "
strsuccess &= " ( `username ` ) VALUES ( "
strsuccess &= " '" & GlobalV.strUsername & "');"
x.dbCommand = strsuccess
x.ExecuteNonQuery()

strTime = "Update(sandbox.timespent) "
strTime &= " SET UID = UID , Username = '" & GlobalV.strUsername & "';"
strTime &= " WHERE(uid = uid) "
x.dbCommand = strTime
x.ExecuteNonQuery()

not sure if i did that part right but what I want to do is add a new UID to the form and add the username right next to that UID as a new session kind of deal... once the user proceeds to the next form this code is executed:

strjob = "Update(sandbox.timespent) "
strjob &= " SET job_description = '" & lstJobList.SelectedItem & "'"
strjob &= " WHERE username = '" & GlobalV.strUsername & "';"
x.dbCommand = strjob
x.ExecuteNonQuery()
x = Nothing

i'm pretty sure i'm not doing that part right, i want the job item to be added right next to the username... but what's happening is, the job item is filling in everywhere it finds the username to be the variable value...

i'd like to have it added to the newest UID, which would only add once... that's why i mentioned possibly making it so " WHERE UID = " some kind of uid variable "

it's a little hard to explain but if you can figure out how to add the job item once to the newest column i'd really appreciate it. thanks!

|||

OK, I see a couple of problems.

A couple more questions.

Where is the UID created?

Is it a IDENTITY field value from the table?

And What version/Edition of SQL Server? ( 2000 / 2005 ) ( Express / Standard / Enterprise )

|||the UID is unique and is incrimented by 1, it's the primary key if that helps.|||

Kenny,

You didn't respond about the version/edition of SQL Server, so I will assume that you are using SQL 2005.

This will not execute in your application, but it should point you in the direction that you need to go to make it work as you desire.

First, you need to capture the UID into a newly created application variable (scope it appropriately).

To do so, change the query type to ExecuteQuery so you can get a resultset.

Change the query to your version of this:

INSERT INTO SandBox.TimeSpent ( UserName )
OUTPUT inserted.UID
VALUES ( GlobalV.strUserName )

The application will do something like:

GlobalV.UID = x.ExecuteQuery

At the execution of the insert query, you will have the UID for the newly inserted row -which contains the UserName.

Then your UPDATE statement should be somewhat like this:

UPDATE SandBox.Timespent
SET Job_Description = {Your listbox.SelectedItem for JobDescription}
WHERE UID = GlobalV.UID

By using the UID, you will UPDATE ONLY the row you desire. AS you noticed, when you UPDATE by UserName, you are updating all rows with that UserName.