Comparing 2 Tables in same database with update

[jacquelinewalton]

Sorry guys... Newbie here... I could really use some help.
I have a proejct where I want to compare 2 MSSQL tables and update only one of them.

Example: I have..

tblCancel with fields cancel_username and cancel_datetime (With this format 7/7/2004 4:38:25 PM)

and

tblUsers with fields UserName and MemberLevel

I want to first check tblCancel for all records with today's date, get all the cancel_usernames tht apply then match those with the UserName in the tblUsers table and set the MemberLevel Field in that table to "Basic"

I have experience in using one table but not multiple tables.

Thanks in advance for any help given. I reallllly appreciate it!!

 

[jacquelinewalton]

This is probably a given but I want to do this in ASP

 

[Tarwn]

Ok, lets break this down:

1) Get all usernames with todays date in the cancel_datetime field

2) Set the memberlevel to basic for each of those users in the tblUsers table


So we can do (1) simply by querying like so:
"SELECT cancel_username FROM tblCancel WHERE convert(varchar,cancel_datetime,101) = convert(varchar,GetDate(),101)"
this gives us all usernames where the mm/dd/yyyy of the cancel_date equals the mm/dd/yyyy of the current date (today).

Now you could complicate matters by embedding that SQL statement inside the next one, or you could apply some ASP code to build a list of usernames.
if you build a comma-delimited list of usernames then you could update the first table like so:

'assume we got results from first back into rs_cancels

Dim user_list
If Not rs_cancels.EOF Then rs_cancels.MoveFirst
Do Until rs_cancels.EOF
   user_list = user_list & "'" & rs_cancels("cancel_username") & "',"
   rs_cancels.MoveNext
Loop

'trim off last comma
user_list = Left(user_list,len(user_list)-1)

sql_update = "UPDATE tblUser SET MemberLevel = 'Basic' WHERE UserName IN (" & user_list & ")"

Then you would just execute that update statement.


Now the other method would be to embed the first SQL statement into the second, but I don't know if you want to go that far into it.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[jacquelinewalton]

OK I think I could get the second one to work... but because Im new and I want to learn all I can will you please show me how the embedded SQL statement would look?

THanks for the quick response I appreciate it.

 

[Tarwn]

I am by no means an SQL expert, so I would likely start with somethig that was kinda right and work torwards something that actually worked

With that said, he ir a statement that probably isn't quite correct but could be fiddled round with till it works:

"UPDATE tblUser SET MemberLevel = 'Basic' WHERE UserName IN (SELECT cancel_username FROM tblCancel WHERE convert(varchar,cancel_datetime,101) = convert(varchar,GetDate(),101))"

At least it looks logical to me, but I can't rememberif the IN comparator takes an inner select statement or if it is restricted only to comma delimited lists.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[jacquelinewalton]

Still workin on it but thanks alot for the reply