global update

Hi all, I need to update some data in a table, based on some criteria.
In this case we are talking about the stamping of a price against a job.
The update table holds the jobs, and the update_details table holds the
activities performed on each job and the cost for each activity. If i
pull back this information using the following code

select t1.reference,t1.update_id, t2.*
from update t1, update_details t2
where left(t1.reference,2) in ('EA','ND','SD','ST')
and t1.update_id = t2.update_id

I get something like

EA 1883 Act1 4.20
EA 1883 Act2 3.00
EA 1883 Act3 7.50
EA 2444 Act1 4.20
SD 5433 Act1 5.60

I need to update the cost for everything pulled back using the above
sql, to a price determined in another table (activities)

the activities table would look something like

Activity_Code Cost_London Cost_Roc
Act1 5.60 4.20
Act2 4.00 3.00
Act3 6.20 5.60

in a nutshell i need to update the cost in the update details from
Cost_roc to Cost_london for all activities for all jobs in the update
table that have a referance starting with specific letters. The new
prices need to be obtained from the activities table.

Would be very gratefull for any help on this matter

Regards,

Ian Selby

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ian Selby (ian.selby@.lojics.co.uk) writes:
> Hi all, I need to update some data in a table, based on some criteria.
> In this case we are talking about the stamping of a price against a job.
> The update table holds the jobs, and the update_details table holds the
> activities performed on each job and the cost for each activity. If i
> pull back this information using the following code
>...

Your question seems to have been left unanswered, and unfortunately I
cannot provide any answer to you. The reason for this, is that I cannot
understand how the values in the Cost_London and Cost_Roc column
maps to the rows in the first result set.

The standard recommendation for getting help with a query is to post:

o CREATE TABLE scripts of the tables involved. (It helps to include
PRIMARY KEY and FOREIGN KEY references.
o INSERT statements with sample data.
o The result you want given the sample data.

This makes it easier to understand what you after, and also it makes it
possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Global Search Criteria - 1 criteria for multiple columns

I am curious of other's opinions on this. It may be more a UI issue than a
database one, but there is a database side to it, and I have seen that folks
here have a broader knowledge base than just proper table structures, so...
I have an application where we need to search for an employee. You can
search on name, employee ID, or department. The determination has been made
to allow the user to enter one value, and then search for the value in any
of the above columns.
i.e.
employee_id = @.SearchString
or
employee_name like '%' + @.SearchString + '%'
or
department_name like '%' + @.SearchString + '%'
The question is, what has your experience been with this approach, from both
a database perspective, and an end user / UI perspective.One technique in such situations is to use a computed column like:
CREATE TABLE tbl (
...,
employee_id ...,
employee_name...,
department_name...
comp_col AS ( employee_id + employee_name + department_name ) );
and create an index on this comp_col column.
Now change your search pattern in your query to use comp_col LIKE '%' + @.p +
'%'. Depending on your usage you can create a covering index on all the
three columns & avoid the computed column altogether as well.
Anith|||Thanks, I'll give that approach a try to help performance.
Does anyone have any non-database related feedback on this approach?
Usability, clarity to the end user, etc?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
+
> '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
> + '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>
Anith,
Could you explain the performance advantage to using either the computed
column or the covering index compared to just a straight where clause (e.g.
WHERE employee_id + employee_name + department_name LIKE '%' + @.p + '%' ) ?
I'm not knocking the approach, I would just like to understand. Also, it
seems as though once you use the LIKE clause, any advantage to an index
disappears.
Thanks,
Kevin|||I think I messed this one up :-( You are right, the wild card in the
beginning of the parameter will nullify the index usage.
Anith