Updating Entire Column

[nickiii]

Hi All:

I have three columns in a table but only need to update one with new values. Columns are:

LocationNum -- Date -- ProfileID

I have a page that allows my users to update the profile ID and submit the change to the database. Every single day in the month is listed with a specfic profile id. So, for July there are 31 dropdowns.

How can I update or insert the database a new column of data leaving LocationNum and Date in tact?

Thanks!

[Hack]

Something likeUPDATE tablename SET profileid = '" & whatever ever & "' "
WHERE locationnum = whatever
AND [date] = whateverPlease not I put date in brackets.

Date is a reserved word and should not be used for field names. Doing so can cause problems.

[nickiii]

Something likeUPDATE tablename SET profileid = '" & whatever ever & "' "
WHERE locationnum = whatever
AND [date] = whateverPlease not I put date in brackets.

Date is a reserved word and should not be used for field names. Doing so can cause problems.

Problem here is the " & whatever ever & " portion will be an array of values. I'm trying to update one column with several different values (without running the proc multiple times). The various values correspond to different dates (which is a column in itself called "date").

I hope that was not confusing ...

Can this be done?

[Hack]

You will need to run the procedure for each separate value.

Each separate value will update all records that meet the criteria of your query. However, each value must be treated separately.

[nickiii]

You will need to run the procedure for each separate value.

Each separate value will update all records that meet the criteria of your query. However, each value must be treated separately.

Ok thanks. I figured that but needed someone else to say it, I guess. :)

[Slope]

I don't know what database your are using but you might try doing something like this (SQL Server syntax):

UPDATE myTable
SET profileid =
CASE
WHEN [date] = '01/01/2008' THEN 1001
WHEN [date] = '01/02/2008' THEN 1002
WHEN [date] = '01/03/2008' THEN 1003
WHEN [date] = '01/04/2008' THEN 1004
WHEN [date] = '01/05/2008' THEN 1005
WHEN [date] = '01/06/2008' THEN 1006
WHEN [date] = '01/07/2008' THEN 1007
END
WHERE ...


Not sure if this will work for you since you have different dates all the time.

I'm not clear as to exactly what you need to do. For example, where do the new profile id's come from? Do you have these values in a table somewhere or is this something that the user enters? It seems to me that if you have it in a table you should be able to run an update query by joining to that table somehow.