Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNameSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so. Still takes a long time. I then notice what is happening. The SQL is taking the SOUNDEX of the LastName of the first record, apply it to ALL the records, then taking the SOUNDEX of the LastName of the second records, then updating it to ALL the records, etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
>
How did you notice this? This is really strange, I've never heard of
something like this.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems in selecting record.

I have a table A which has the following structure:
Column Name(Type)
-- --
ID(int)
Repair Code(varchar)
Damage Code(varchar)
Location Code (varchar)
I also have a the master code table
Column Name(Type)
-- --
Code Type (int)
Code Name (varchar)
Code Description ( varchar)
and have a stored procedure called GetCodeDescription which input the Code
Type and Code Name , display the Code Description found in the code master
table.
I want to select the record from TabelA by inputting a ID , then select the
record which have the follwong structure
Column Name(Type)
--
ID (int)
Repair Code (varchar)
Repair Code Description (varchar)
Damage Code (varchar)
Damage Code Description (varchar)
Location Code (varchar)
Location Code Description (varchar)
How can I do it by using the GetCodeDescription stored procedure?
Thnak you very much !Trying to use the stored procedure is only going to make things much,
much more complicated than necessary. How about just doing a query?
SELECT A.ID,
A.RepairCode, B.COdeDescription as RepairDescription,
A.DamagerCode, C.COdeDescription as DamageDescription,
A.LocationCode, D.COdeDescription as LocationDescription
FROM TableA as A
JOIN MasterCodes as B
ON A.RepairCode = B.CodeName
AND B.CodeType = 1
JOIN MasterCodes as C
ON A.DamageCode = C.CodeName
AND C.CodeType = 2
JOIN MasterCodes as D
ON A.LocationrCode = D.CodeName
AND D.CodeType = 3
Roy
On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
<BallBall@.discussions.microsoft.com> wrote:

>I have a table A which has the following structure:
>Column Name(Type)
>-- --
>ID(int)
>Repair Code(varchar)
>Damage Code(varchar)
>Location Code (varchar)
>I also have a the master code table
>Column Name(Type)
>-- --
>Code Type (int)
>Code Name (varchar)
>Code Description ( varchar)
>and have a stored procedure called GetCodeDescription which input the Code
>Type and Code Name , display the Code Description found in the code maste
r
>table.
>I want to select the record from TabelA by inputting a ID , then select the
>record which have the follwong structure
>Column Name(Type)
>--
>ID (int)
>Repair Code (varchar)
>Repair Code Description (varchar)
>Damage Code (varchar)
>Damage Code Description (varchar)
>Location Code (varchar)
>Location Code Description (varchar)
>How can I do it by using the GetCodeDescription stored procedure?
>Thnak you very much !
>|||Thank for the answer , but because my code table container about 12000
records, if i join many times , i think the performance will be affected
"Roy Harvey" wrote:

> Trying to use the stored procedure is only going to make things much,
> much more complicated than necessary. How about just doing a query?
> SELECT A.ID,
> A.RepairCode, B.COdeDescription as RepairDescription,
> A.DamagerCode, C.COdeDescription as DamageDescription,
> A.LocationCode, D.COdeDescription as LocationDescription
> FROM TableA as A
> JOIN MasterCodes as B
> ON A.RepairCode = B.CodeName
> AND B.CodeType = 1
> JOIN MasterCodes as C
> ON A.DamageCode = C.CodeName
> AND C.CodeType = 2
> JOIN MasterCodes as D
> ON A.LocationrCode = D.CodeName
> AND D.CodeType = 3
> Roy
>
> On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
> <BallBall@.discussions.microsoft.com> wrote:
>
>|||Try the join first. You will probably find the performance is just fine,
and simpler to manage.
"BallBall" <BallBall@.discussions.microsoft.com> wrote in message
news:36B2D0B0-6201-42BE-933B-079D4FCE44DE@.microsoft.com...
> Thank for the answer , but because my code table container about 12000
> records, if i join many times , i think the performance will be affected
> "Roy Harvey" wrote:
>
Code
master
the|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative and pseudo-code are useless.
That would be a horrible design error! Go back to the foundations; a
table is made up of one and only one kind of entity. There is no such
thing as "master code table" in a valid schema.
Next, a data element can be a type or a code but not a "type_code";
read ISO-11179. Next you confuse rows with records and columns with
fields and do not bother to give us table names.
Finally, how do you get enough codes into a VARCHAR(1) column? In a
good design, codes are fixed length and have a validation rule. There
is no such thing as a magical universal "id" in RDBMS; you are not
actually using IDENTITY in an RDBMS, are you'
I have to make a wild guess about the key
CREATE TABLE RepairRequests
(repair_nbr INTEGER NOT NULL,
repair_code INTEGER NOT NULL
REFERENCES Repairs (repair_code)
ON UPDATE CASCADE,
PRIMARY KEY (repair_nbr, repair_code),
damage_code INTEGER NOT NULL
REFERENCES Damages(damage_code)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations (location_code)
ON UPDATE CASCADE);
CREATE TABLE Repairs
(repair_code INTEGER NOT NULL PRIMARY KEY,
repair_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Damages
(damage_code INTEGER NOT NULL PRIMARY KEY,
damage_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Locations
(location_code INTEGER NOT NULL PRIMARY KEY,
location_description VARCHAR(20) NOT NULL,
.) ;
I have no idea; where is the code for this stored procedure?
I see that you also do not understand what a repeated group is and how
to program in a tiered architecture. This should be done as a simple
query. But assuming that you really do hate RDBMS, how do you handle
a repair request with more or less than two damages on it?|||>Thank for the answer , but because my code table container about 12000
>records, if i join many times , i think the performance will be affected
If performance is bad the table is not indexed correctly. Also it is
not necessary to guess what performance will be, all you have to do is
run the queries in Query Analyzer to see what it is. Easy, quick, and
you can learn a lot.
Twelve thousand rows is really all that large, by the way. With
proper indexing performance should be fine with ten or a hundred times
as many rows.
Roy

Problem with variable of type money?

Here's what I want to do: I've got a table with orders, each order has
a specific discountrate (an int, which represents a percentage). Each
order consists of 1 or more items in another table, each item in that
table has a price. Now I want to return the full price and the
discounted price (or the discounted amount).

Here's a relevant excerpt of the code:

--------------------
CREATE TABLE #tmp (OrderID Integer,
Price money,
Discount money)

DECLARE @.Discount money

SELECT @.Discount =
(
(
(SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
FROM OrderDetails
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
+
(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
OrderDetailsAmount),0)
FROM OrderDetailsSupplement
INNER JOIN OrderDetails ON
OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
)
*
( @.DiscountRate / 100 )
)

SELECT CustomerFull,
SUM(Price) As Price,
SUM(Discount) As Discount,
SUM (Products) As Products,
COUNT(@.orderID) As Orders
FROM #tmp
GROUP BY CustomerFull
ORDER BY CustomerFull
--------------------

The problem: instead of getting a low number (like 0.57 for instance),
I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
code, but I'd really like to know what I'm doing wrong.

--
BVHAm 2 Mar 2006 07:23:09 -0800 schrieb bartvanhemelen@.gmail.com:

> Here's what I want to do: I've got a table with orders, each order has
> a specific discountrate (an int, which represents a percentage). Each
> order consists of 1 or more items in another table, each item in that
> table has a price. Now I want to return the full price and the
> discounted price (or the discounted amount).
> Here's a relevant excerpt of the code:
> --------------------
> CREATE TABLE #tmp (OrderID Integer,
> Price money,
> Discount money)
> DECLARE @.Discount money
> SELECT @.Discount =
> (
> (
> (SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
> FROM OrderDetails
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> +
> (SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
> OrderDetailsAmount),0)
> FROM OrderDetailsSupplement
> INNER JOIN OrderDetails ON
> OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> )
> *
> ( @.DiscountRate / 100 )
> )
> SELECT CustomerFull,
> SUM(Price) As Price,
> SUM(Discount) As Discount,
> SUM (Products) As Products,
> COUNT(@.orderID) As Orders
> FROM #tmp
> GROUP BY CustomerFull
> ORDER BY CustomerFull
> --------------------
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

In your example i can't see where @.DiscountRate is declared or set. From
where should the value for @.DiscountRate come?

bye,
Helmut|||BVH,

You are probably the victim of integer arithmetic.

Change @.DiscountRate / 100 to @.DiscountRate / 100.0.

That "point zero" tells the system that you want float division,
instead of integer division. The better solution would be to cast the
integer to a float but adding .0 will work.|||(bartvanhemelen@.gmail.com) writes:
> *
> ( @.DiscountRate / 100 )
> )
>...
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

@.DiscountRate was integer, correct?

You should have left out the parentheses above. This mandates SQL Server
to compute this expression before it gets mixed with the rest. But if
you divide two integers, you get integer division, which is not what you
want at all.

Assuming that what is before the * is money, leaving out the parenthesis,
transforms the division to money division.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||what happens next week when the boss wants to give a five and a half
percent discount???

percentages are ALWAYS better stored as real, and multiplied by 100 for
display to the users.|||I agree with Doug. We usually use reals to hold our percentages. In the
db, a 5.5% discount would look like .055.......

Because, in effect, that's really what 5.5% represents.|||figital (mharen@.gmail.com) writes:
> I agree with Doug. We usually use reals to hold our percentages. In the
> db, a 5.5% discount would look like .055.......
> Because, in effect, that's really what 5.5% represents.

In our shop we can never make up our mind... So some of the percentages
are stored as aba_percent, others as aba_fraction and yet others as float.
aba_percent is just an alias for "float" but the name implies that it is
a percentage, and that you should divide with 100 before use. aba_fraction
is float, and constrained to be between 0 and 1. Multiply with 100 before
display, and divide by 100 before storing. Those that just float, can
hold values outside the range [0..1]. (I have considered a constraint
to keep them between -10 and 10, but that is a risky business, as one
day 1200% may be a correct value.)

The problem with storing percentages as fraction, is that some developers
make the entry forms a carbon of the data model, so they don't display
the fraction as a percentage, but as a fraction...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>ba_fraction is float, and constrained to be between 0 and 1

Well, actually, I've used percentages to keep track of growth rates, or
percentage growths where the percentage can greatly exceed one.

For instance, 2.00 means 200 percent, which means multiply by 200
percent.

Another really good thing about storing 5 percetn as .05 is that the
very first time the programmer/UI dweeb displays it that way, the
programmer dweeb sees it.

At the VERY worst, the end user sees it, and pretty much ANYONE used to
dealing with money, growths, or numbers will report it as a pretty
minor bug, and realize what is going on.
Again, differing points of view arrived at by rational people, but IMO
float is a much better solution.
regards,
doug

problem with varbinary(max) in SQL Server 2005

Hi, we have stucked with strange problem. Please read comments in the following script.

CREATE TABLE ItemsTable1(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (max) NULL
)

CREATE TABLE ItemsTable2(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (20) NULL
)

-- insert same values in both tables

INSERT INTO ItemsTable1(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

INSERT INTO ItemsTable2(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

--query 1 returns nothing. SQL Server thinks that values are different. Why?
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemNameCode = it2.ItemNameCode

--query 2 returns 'Equals!!!'.
SELECT CASE WHEN it1.ItemNameCode = it2.ItemNameCode THEN 'Equals!!!'
ELSE 'Not equals!!!' END
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemId = it2.ItemId

--query 3 after casting all works fine.
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE CAST(it1.ItemNameCode as varbinary(20))
= CAST(it2.ItemNameCode as varbinary(20))

DROP TABLE ItemsTable1
DROP TABLE ItemsTable2

Note: If we use nvarchar(20) and nvarchar(max) all works fine. So may be this is a bug?
Looks like a bug to me|||This is a bug. And it is not fixed in a recent build of SQL Server 2005 SP1 either. Could you please file a bug for this in the MSDN Product Feedback Center? Thanks.

Problem with Update query

Hi - I have 2 tables, which I would like to 'merge'.
tblAccess and tblCars
tblAccess:
a_id int (identity)
a_user (varchar50)
a_pass (varchar20)
a_cid (bigint)
tblCars:
c_id (identity)
c_user (varchar50)
c_carname (varchar50)
These were joined using the a_cid to c_id field, but now I want to copy
the tblAccess.a_user into the relative record in the tblCars.c_user
table.
Something like:
Update tblCars set tblCars.c_user = (select tblAccess.a_user from
tblAccess WHERE tblAccess.a_id = tblCars.c_id)
But I get an error advising the subquery returns more than 1 result.
Could anyone please help?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***>> But I get an error advising the subquery returns more than 1 result.
The error message suggests that you have more than one value for a_id in the
tblAccess table for each value of c_id in the tblCars table.
If you want to get the right value, you might have to look carefully into
the logic you use in the WHERE clause in your subquery. If all you want to
do is to avoid the error, then you can use an extrema aggregate like:
UPDATE tblCars
SET c_user = ( SELECT MAX( tblAccess.a_user )
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
WHERE EXISTS ( SELECT *
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
The WHERE clause is to constrain the statement to update only the values
that have matching values for the identifier columns in both the tables.
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I guess that you meant to post something like this, assume that you
know that IDENTITY is not ever a key , how to do proper data element
names:and followed industry standards. And not put "rbl-" suffixes on
table names or table anem suffixes on column names.
This is still bad, but it is not awful:
CREATE TABLE VehicleAccess
(vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
vehicle_pass VARCHAR (50) NOT NULL,
vin CHAR(17) NOT NULL --industry std
REFERENCES Cars(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (vehicle_user, vehicle_pass) -- wild guess by me
);
CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY, --industry std
vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
car_name VARCHAR (50) NOT NULL -- you name your car?
);
First of all, fields are not anything like columns and rows are not
anything like records! No wonder you were using the word "merge" in
this posting -- you are locked into a file system model of data, not an
RDBMS.
Next, a data element has one and only one name in a schema. It does
not change from table to table. But file systems do not have a data
dictionary, so you missed this basic point.
Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.
Of course; this design has no data integrity. You cannot trust
anything you get out of it. This attempt at kludging an unusable
design only showed you a FEW of the problems you have.
Start over. What are the attributes of a car? The VIN is the natural
key. But why does a car have a name? Does it come when you call it?
And even if it have a name, why is it soooooo long?
You need a table of vehicle users (they are entities, aren't they?
Model them!).
You need a table of vehicle assignments (or access rights) It will
reference the Cars and the Users, but also have a date range, the users
role (driver, passenger), etc.
You used over-sized columns -- Why did you pick BIGINT and VARCHAR(50)?
They will only accumulate garbage. You have no DRI actions. YOu have
no constraint.
Oh, and remember to do a full data audit to clean up what you have in
the DBMS now.