Help! Two-Part SQL Update

I have a child table with semicolon-delimited data in a single
column. Based on what is in the first part of the semicolon-delimited
data, I need to write a value to a column in the parent table and
then remove that first part of the child-table column. I guess I want
to do a
two-part SQL Update, but am not sure where to begin. The tables
look like this:
Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
)
Insert Into #ParentInfo( KeyCode )
Values( 'E1B296FCSYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '85829EDESYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( 'A5CB9CF5SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '4CCF9C15SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '40B5DB72SYSTEM' )
Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
VarChar(900) )
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
in the Status column if "Demo Import" is in #ChildInfo.Comments, and
then remove
"Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
Thanks.something like this should do:
begin tran
update p
set status = case when c.comments like 'Demo Import%' then 'Imported' else
status end
from #ParentInfo p, #ChildInfo c
where p.KeyCode=c.KeyCode
and p.KeyCode='E1B296FCSYSTEM'
if @.@.error<>0 rollback tran
update #ChildInfo
set comments = replace(comments,'Demo Import','')
where KeyCode='E1B296FCSYSTEM'
if @.@.error=0 commit tran
else rollback tran
-oj
"xenophon" <xenophon@.online.nospam> wrote in message
news:vfp0a1t3mn9r022b5r96h79srejao9ec05@.
4ax.com...
>
> I have a child table with semicolon-delimited data in a single
> column. Based on what is in the first part of the semicolon-delimited
> data, I need to write a value to a column in the parent table and
> then remove that first part of the child-table column. I guess I want
> to do a
> two-part SQL Update, but am not sure where to begin. The tables
> look like this:
>
> Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
> )
> Insert Into #ParentInfo( KeyCode )
> Values( 'E1B296FCSYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '85829EDESYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( 'A5CB9CF5SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '4CCF9C15SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '40B5DB72SYSTEM' )
> Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
> VarChar(900) )
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>
> So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
> in the Status column if "Demo Import" is in #ChildInfo.Comments, and
> then remove
> "Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
> Thanks.
>
>
>|||I hope those are not the real names. A data element can be a code, but
it is *used* as a key, so key_code is nonsense. You NEVER name a data
element for how it is used in the physical schema; you name it for what
it is in the data model.
ParentInfo is also weird -- singlular so we have only one parent and
are there tables that do not store information? Likewise, a name like
status does not tell us "status of what?" when we read it.
Try something like this and avoid dangerous proprietary UPDATE ..
FROM.. syntax.
BEGIN
UPDATE Parents
SET foobar_status
= 'Imported'
WHERE EXISTS
(SELECT *
FROM Children AS C
WHERE Partents.foobar_id = C.foobar_id
AND comments LIKE 'Demo Import ; ' + '%');
UPDATE Children
SET comments = REPLACE(comments, 'Demo Import ; ','');
END;
While we have no DDL or specs, this scares me. You are in violation of
First Normal Form and it look like you are physically moving data from
table to table. That is how we did it with punch cards in the
1950's. You might want to talk to someone with RDBMS design
experience.|||1.
These are not real names. All names were changed and stripped
down to protect the guilty. :)
2.
The data was originally a mess, and what I was looking for was
a streamlined way to clean it up - this is a large multi-multi-
step process.
So there it is.
Thanks.
On 3 Jun 2005 11:59:46 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>I hope those are not the real names. A data element can be a code, but
>it is *used* as a key, so key_code is nonsense. You NEVER name a data
>element for how it is used in the physical schema; you name it for what
>it is in the data model.
[snip]

Help! Got an error while do the replication update

Dear all,
I got the following error while doing the replication in updating the date,
I found that a column's datatype is NTEXT, but i have other table also are
have columns set to NTEXT datatype and it works.
Does anyone have any idea on it?
"Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table."
can you post your schema here for the problem table? Also do you recall what
update/insert/delete caused this problem?
Perhaps try to restart your agent and log according to:
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Madstan" <stanley.chong@.hk.mrspedag.com> wrote in message
news:uQdRmGKvEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> I got the following error while doing the replication in updating the
date,
> I found that a column's datatype is NTEXT, but i have other table also are
> have columns set to NTEXT datatype and it works.
> Does anyone have any idea on it?
> "Only text pointers are allowed in work tables, never text, ntext, or
image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table."
>

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
JEither backup the transaction log, restore the last backup and reapply =
transaction logs up to just before the unwanted update,or restore the =
most recent backup to a separate db and copy the offending table to your =
real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =
need to
> reverse the effect. How can I do this.
>=20
> Thanks
>=20
> J
>=20
>|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
JEither backup the transaction log, restore the last backup and reapply =transaction logs up to just before the unwanted update,or restore the =most recent backup to a separate db and copy the offending table to your =real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =need to
> reverse the effect. How can I do this.
> > Thanks
> > J
> >|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
J
Either backup the transaction log, restore the last backup and reapply =
transaction logs up to just before the unwanted update,or restore the =
most recent backup to a separate db and copy the offending table to your =
real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =
need to
> reverse the effect. How can I do this.
>=20
> Thanks
>=20
> J
>=20
>
|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

Help! can't run Update in SQL 2005

I have a table name : sale there have some item about (SN(int,PK),...Invoice(nvarchar)....)

try run a update query in SQL 2005 :

update sale set Invoive = '99999' where SN in('1','2','3')

Get the error msg:

Msg 512, Level 16, State 1, Procedure Update_Cost, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

but run in SQL 2000+SP4 is OK!

what's wrong in SQl2005?

this is working for me,

or

just send us the full test data(Table structure + Test data), check is there some trigger there for this table? go for the same statment for other table, i mean update same fileds, or so.

|||

From your description, SN is integer. The single quotes around the integers should be removed.

UPDATE sale SET Invoice = '99999' where SN in (1, 2, 3)

Martin Poon [MVP - SQL Server]

|||

Thanks a lot!

Find the root cause! after check the DB find there have trigger with updat invoice!

thanks again!

Help! - BCP / Bulk Copy

I need top update several tables with new values from an external text file. That would notrmally be a no brainer, (even for me) :) But, what is important this time around, is I can NOT fire the triggers, or every user would get notified of a 150,000 updates.

The text file contains 4 fields
KEYNUM|UpdateField1|UpdateField2|Class

Class will be either 1 or 2, if class = 1 then table 1 contains the record, if class = 2 then table 2 contains the record...

Any help, as always is greatly appreciatedHi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?|||Originally posted by bmalar
Hi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?

That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)|||Originally posted by GregCrossan
That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)

Sorry I didn't read your post correctly.. I thought you were inserting new records.

Help with varchar to date

I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!

> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be between an Australian date
format and a US format.

> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.

>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm now.
Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be between an Australian date
> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:

> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!

> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?

Help with UPDATE with SqlDataSource and a Repeater

Hi,

I have a repeater which i need to build editing capabilities into in a similar way as the GridView, just a little unsure how to tie it together. Ideally i would like to use a GridView as this would do all of the work for me however it looks like I need to go with the repeater due to the nested nature of the data I am working with.

What is the best way to go about updating the data source from within a repeater? I have already built the edit interface with some MultiViews in the Repeater control and handling the ItemCommand event, just not sure how to actually update the data source.

Can I still use the SqlDataSource wizard to generate all of the SQL for me? If so, within the ItemCommand event handler I have the data i want to update back to the database by using the FindControl method and retrieving the Text, but how do i get this data into the SqlDataSource object and update the database?

Are there any samples available which show how to update a datasource using a repeater?

Your help is much appreciated

trenyboy

Have you thought about using a DataList, this is halfway between the GridView and the Repeater, but it's certainly easier to work with :-)

Have a look at thissample for an example of DataList binding, and thisone for an example of updating.

|||

No, I hadn't up until now! I was using the repeater because I have nested data which I needed to appear as if it were part of the same table, hence I need to render the html myself, but I see the DataList will also allow for this!

Does the DataList have support for Edit/Update in a similar way as the GridView does with using the SqlDataSource wizard and no custom code (ASP.NET 2.0)? If not, what would be the advantage for using the DataList over the Repeater?

Thanks

|||I'm not sure what you mean by "nested data". If you could explain that a little better I'm sure someone could come up with a good solution for you. Is the nested data from the SQL Server, and you want to add some more columns on to it, or is the nested data something being generated by the ASP.NET application like in an array/list?|||By nested data I am referring to parent/child relationships. Ihave summary (parent) rows which contain one or more detail (child)rows. All rows need to look as though they are part of the sametable, however, the parent rows provide an option to show or hide allof its related child rows.

What I have done to get around this is to use one repeater for theparent data, and another repeater for the child data, this way I ammanually rendering the html as a single table, but I can show/hide thischild repeater via its visible property.

It is from SQL Server, and all i want to be able to do is provide anediting functionality to allow the user to update the underlyingdatasource from the interface.

Does this make more sense?|||

Yes it does. I've never done that myself, but perhaps someone else here has.

If it were me, I would use a query to return all the data I wanted in the order I wanted, then use the gridview, and write my own javascript to hide/show the child rows.

|||

trenyboy wrote:

Does the DataList have support for Edit/Update in a similar way as the GridView does with using the SqlDataSource wizard and no custom code (ASP.NET 2.0)? If not, what would be the advantage for using the DataList over the Repeater?

No it doesn't. What I've always done in the past is implement my DataList_UpdateCommand and simply call the update on the DataAdapter that is Filling the DataSet that backs up the DataList. Then you can use QueryBuilder to generate the Update for you.

In fact I'm starting to think I might be able to use an ObjectDataSource here, I may have to experiment a little :-)

HELP with Update with Join

Ive been trying to get this to work for a while now and I cant find anything in the forum that works. I am using DB2 UDB v8.1 for windows. Here the problem:

I have 2 tables

Product Table
----
ID (Primary key)
Vendor_ID

Warehouse Table
-----
Product_ID (Foreign key to Product.ID)
Reorder_Level

I want to update all Warehouse.Reorder_Level on products that have a Vendor_ID of 1.

I have tried the following statements without any luck:

Update Warehouse set Reorder_Level=5 from Warehouse inner join Product on (Warehouse.Product_ID = Product.ID and Product.Vendor_ID=1)

Update (Select Warehouse.* from Product, Warehouse where Product.ID = Warehouse.Product_ID and Product.Vendor_ID=1) as temp set temp.Reorder_Level
=5

Update Warehouse, Product set Warehouse.Reorder_Level=5 where Product.ID = Warehouse.Product_ID and Product.Vendor_ID=1

Im out of ideas and very frustrated Please HELP!I don't know db2, but msql would be

UPDATE Warehouse
SET Reorder_level = 5
WHERE EXISTS
(SELECT *
FROM product
WHERE id = product_id AND vendor_id = 1)|||It works... Thank you!

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

--
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

Help with UPDATE trigger

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain.com',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain.com',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email serverl'
select RC = @.rc
END

goYou need to check for the updated value.|||I'm sorry, but I am new to SQL.
Where and how do I insert CHECK|||Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
set nocount on
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
IF @.CDStatus = 'In Review'
BEGIN
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email server'
select RC = @.rc

END
END
go

Help with UPDATE statement! TY!

Given the table (mytable)
my_id int (pk)
my_type char(1)
my_version tinyint
my_datetime datetime

Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 null
7 b 2 1/5/03
8 c 2 null
9 d 2 1/5/03
10 e 2 1/6/03

I want to write an update statement that will set all version 2
datetimes to their version 1 value when the version 2 value is null

After the update the data should look like:
Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 1/1/03
7 b 2 1/5/03
8 c 2 1/3/03
9 d 2 1/5/03
10 e 2 1/6/03

I've tried:

update my_table
set my_datetime = v1.my_datetime
from
(select *
from my_table
where my_version = 1 and my_datetime is not null) v1,
(select *
from my_table
where my_version = 2 and my_datetime is null) v2
where v1.my_type = v2.my_type

But this just updates all version 2 rows to the lowest date.

What am I doing wrong?

TIACan I assume that there is only one row where my_version=1 for each value of
my_type? If so:

UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<mdydnYICIoU7kxGiRVn-sQ@.giganews.com>...
> Can I assume that there is only one row where my_version=1 for each value of
> my_type? If so:
> UPDATE MyTable
> SET my_datetime =
> (SELECT my_datetime
> FROM MyTable AS M
> WHERE my_version = 1
> AND my_type = MyTable.my_type)
> WHERE my_datetime IS NULL

David, yes you can assume that. There are more versions than just 1 &
2 though so I modified your where statement to add:
UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL and my_version = 2

and it worked like a charm! THX!

Help with update statement

Hi guys,

I have the following sample data:

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

Now, if in the same paperID, there is a statusKy A and the status date is earlier than the other statusDate, i would like to change the other statusdate to be the same as the status date with the statusKy of 'A'. if there is a statusKy 'A', but the other Statusky contains dates that are earlier than the date in StatusKy 'A', then leave it as what it was.

The result i would want to see :

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-01 00:00:00.000 D

0001 4222 2003-09-01 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-10 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

can you guys help me with this issue? i would appreciate it so much. thanks

You ought to be able to put together a pretty good 2-pass solution if you will update based on a derived table of the target. You MIGHT be able to put together a 1-pass solution using TSQL UPDATE extensions IF you need a faster solution.

|||Hi Kent, I am really a beginner in t-sql, would you precise what you mean by putting a 1-pass solution using tsql update extensions? thanks.|||

If you want to just view the data like this, you can do it like this:

Code Snippet

--including scripts like this will get you better responses
drop table test
go
create table Test
(
PaperID char(4),
StatusID char(4),
StatusDate smalldatetime,
StatusKey char(1),
Primary Key (PaperId, StatusId)
)
insert into Test
select '0001','4566','2003-09-03 00:00:00.000','D'
union all
select '0001','4222','2003-09-03 00:00:00.000','C'
union all
select '0001','4132','2003-09-01 00:00:00.000','A'
union all
select '0002','4222','1999-04-14 00:00:00.000','C'
union all
select '0002','4132','1999-04-10 00:00:00.000','A'
union all
select '0003','4132','1986-08-03 00:00:00.000','A'
union all
select '0003','4566','1986-07-29 00:00:00.000','D'
go

select Test.PaperId, Test.StatusId,
case when Astatus.StatusDate < Test.StatusDate
then Astatus.StatusDate
else Test.StatusDate
end as StatusDate,
StatusKey
from Test
join ( select PaperId, StatusId, statusDate
from Test
where StatusKey = 'A') as Astatus
on Test.PaperId = Astatus.PaperId

|||

Thank you so much, i really appreciate your help. you guys are awesome, thanks again.

Jul.

|||

Jul:

Sorry that I was unable to finish my response. I thought I had about 15 minutes that I could get you an answer but I have been really slammed with DB2 work lately. I should be able to finish my answer in the morning. What I mean by a 1-pass solution is that it only has to traverse the data of the table 1 time.

Kent

|||

If you are using SQL Server 2005 then you can use the query below instead which scans the data only once.

Code Snippet

select t.PaperId

, t.StatusId

, case when t.Status_A_Date < t.StatusDate then t.Status_A_Date else t.StatusDate end as StatusDate

, t.StatusKey
from (
select *, min(case StatusKey when 'A' then StatusDate end) over(partition by PaperId) as Status_A_Date
from Test
) as t;

|||NP Kent, thanks again for your contribution!!!! |||

Jul:

Here is an example of a 1-pass update that uses the TSQL extensions:

Code Snippet

create table dbo.mockup
( PaperID varchar(5),
StatusID integer,
StatusDate datetime,
StatusKey char(1),
)
go

create index mockup_UpdExt_Cvr
on dbo.mockup (PaperID, StatusKey, StatusDate)
go

insert into mockup
select '0001', 4566, '2003-09-03 00:00:00.000', 'D' union all
select '0001', 4222, '2003-09-03 00:00:00.000', 'C' union all
select '0001', 4132, '2003-09-01 00:00:00.000', 'A' union all
select '0002', 4222, '1999-04-14 00:00:00.000', 'C' union all
select '0002', 4132, '1999-04-10 00:00:00.000', 'A' union all
select '0003', 4132, '1986-08-03 00:00:00.000', 'A' union all
select '0003', 4566, '1986-07-29 00:00:00.000', 'D'

declare @.nextDate datetime

update mockup
set @.nextDate
= case when a.StatusKey = 'A' then a.statusDate
else @.nextDate
end,
statusDate = @.nextDate
from mockup a (index=mockup_UpdExt_Cvr)

select PaperID,
StatusID,
convert(varchar(10), statusDate, 101) as statusDate,
StatusKey
from mockup

/*
PaperID StatusID statusDate StatusKey
- -- -
0001 4566 09/01/2003 D
0001 4222 09/01/2003 C
0001 4132 09/01/2003 A
0002 4222 04/10/1999 C
0002 4132 04/10/1999 A
0003 4132 08/03/1986 A
0003 4566 08/03/1986 D
*/

Before going farther what I would suggest is that under normal circumstances it is probably better to use either Uma's or Louis' code rather than employ the TSQL update extension -- tend to use the update extensions sparingly.

I especially appreciate Uma's response because I keep forgetting about the use of the OVER( PARTITION BY ... ) clause being availabe with aggregate functions. Please stay after me until I get this right. It seems like OVER(ORDER BY ...) is not available for aggregates but only for the ranking functions; is that correct?

|||

Now, what if i would like to select only those that have statusdates that are later than statusdates with statusky 'A'?

Table1

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

And i would like to have a result of :

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

I do not want to select 0003 because it has the correct data (statusdate with statusky 'A' is later than the other status dates). additionally, if let say i have a paperID that contains a statusky of 'A' and a statusky of 'D' having the same dates, that would not be selected in the query.

What is the best query to use? thanks.

help with update query!

I have two tables... BillD and NewBillD

BillD has columns [order number], [price], [cost] etc.

NewBillD has just columns [order number], [price]

the order numbers in both tables are the same. I want to update billd with price from NewBillD.

Why will this query not work:

Update billd
set price = newbilld.price
where account = newbilld.account

Thanks!

Kenduh, forgot to join... Is it Friday yet?

help with update query needed

Could anyone help me with an update query? I need to populate Table 1
(bookings) with data from Table 3 (defaults), via a joining field in Table 2
(enquiries). All fields are of type INT, using SQL Server 2000.
Table 1 (bookings):
id, t_val, q_val
Table 2 (enquiries):
id, booking_id, enq_type
Table 3 (defaults):
id, def_enq_type, def_t_val, def_q_val
Table 3 data:
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
The fields to be updated are 'bookings.t_val' and 'bookings.q_val', from
'defaults.def_t_val' and 'defaults.def_q_val' respectively. 'bookings.id'
relates to 'enquiries.booking_id', and 'enquiries.enq_type' to
'defaults.def_enq_type'. Just to make it more complex, 'booking_id' in
enquiries can have duplicates, in which case I want the one with the highest
'enquiries.id' (ie the most recent record).
Any help very gratefully received, I've been staring at it for hours.Tony,
Do you have any DDL for the Tables? And also some sample data for the
Tables?
Any how do you perceive Table 1(Bookings) to look like?
Thanks
Barry|||Try This:
declare @.bookings table(id int, t_val int, q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1, 1, 9)
insert @.enquiries values(2, 2, 11)
insert @.enquiries values(3, 1, 10)
insert @.bookings
select e.booking_id, d.def_t_val, d.def_q_val
from @.enquiries e
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
select * from @.bookings|||Thanks for the replies so far, but I'm still struggling.
I think I should have made it clearer that the table 'bookings' is already
populated with data, and that it is just 2 new columns (t_val, q_val) that
need populating (so obviously I've omitted all other fields not needed for
this update query).
So if 'bookings' currently looks like:
id, t_val, d_val
1, null, null
2, null, null
3, null, null
4, null, null
and 'enquiries' looks like:
id, booking_id, enq_type
1,2,9
2,4,10
3,1,12
4,1,11
5,3,9
(note more than one record for booking_id 1)
and as mentioned before, 'defaults' looks like:
id, def_enq_type, def_t_val, def_q_val
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
then the end result for 'bookings' should be:
id, t_val, d_val
1, 3, 2
2, 2, 1
3, 2, 1
4, 2, 2
Again, any help very gratefully received.
"JeffB" wrote:

> Try This:
> declare @.bookings table(id int, t_val int, q_val int)
> declare @.enquiries table(id int, booking_id int, enq_type int)
> declare @.defaults table(id int, def_enq_type int, def_t_val int,
> def_q_val int)
> insert @.defaults values(1,9,2,1)
> insert @.defaults values(2,10,2,2)
> insert @.defaults values(3,11,3,2)
> insert @.defaults values(4,12,1,2)
> insert @.enquiries values(1, 1, 9)
> insert @.enquiries values(2, 2, 11)
> insert @.enquiries values(3, 1, 10)
> insert @.bookings
> select e.booking_id, d.def_t_val, d.def_q_val
> from @.enquiries e
> inner join @.defaults d
> on e.enq_type = d.def_enq_type
> inner join (select booking_id, max(id) maxid from @.enquiries group by
> booking_id) x
> on e.booking_id = x.booking_id and e.id = x.maxid
> select * from @.bookings
>|||Try this:
declare @.bookings table(id int, t_val int, q_val int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
insert @.bookings values(1, null, null)
insert @.bookings values(2, null, null)
insert @.bookings values(3, null, null)
insert @.bookings values(4, null, null)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1,2,9)
insert @.enquiries values(2,4,10)
insert @.enquiries values(3,1,12)
insert @.enquiries values(4,1,11)
insert @.enquiries values(5,3,9)
update b
set b.t_val = d.def_t_val,
b.q_val = d.def_q_val
from @.bookings b
inner join @.enquiries e
on b.id = e.booking_id
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
where b.t_val is null and b.q_val is null
select * from @.bookings

Help with update query linking 3 tables

I am looking for some assistance with an update query that needs to link 3
tables:

This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?

UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
from absences, cicntp
where (SELECT cicntp.cnt_l_name
FROM cicntp INNER JOIN
gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Next I tried this query which is still running after 75 minutes (on a
laptop)

update absences
set CustomerContactID = cicntp.cnt_id
from absences, cicntp, gm_names
where gm_names.name= cicntp.cnt_l_name

As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
Absences.CustomerContactID is what I need updated, when finished it should
match CICNTP.cnt_id
GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name

Can some of you school this newbie on the best way to do this?
Thanks a bunch!On Fri, 15 Apr 2005 19:54:56 GMT, rdraider wrote:

>I am looking for some assistance with an update query that needs to link 3
>tables:
>This query ran and reported over 230,000 records affected but did not change
>the field I wanted changed, not sure what it did.
>I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
>Analyzer. Is it bad to name a column "name"?
>UPDATE ABSENCES
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp
>where (SELECT cicntp.cnt_l_name
> FROM cicntp INNER JOIN
> gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
> Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Hi rdraider,

I guess that you made a copy & paste error, since this query can never
have reported over 230,000 rows affected - it can only report "Incorrect
syntax near ')'".

>Next I tried this query which is still running after 75 minutes (on a
>laptop)
>update absences
>set CustomerContactID = cicntp.cnt_id
>from absences, cicntp, gm_names
>where gm_names.name= cicntp.cnt_l_name

Depending on the size of the tables, it'll probably run a whole lot
longer - and it likely won't result in the change you wish. Since you
didn't specify anything to link absences to either of the other tables,
this query will effectively:

1. Join cicnt and gm_names on the name column, as specified in the WHERE
clause; then
2. Update EACH row in absences with the value of EACH row in the result
of the join above. If that join yields a million rows, then each row in
absences will be updated a million times. And in the end, the cnt_id
value from whatever row happens to be processed last will end up being
in CustomerContactID for ALL absences!

>As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
>Absences.CustomerContactID is what I need updated, when finished it should
>match CICNTP.cnt_id
>GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name
>Can some of you school this newbie on the best way to do this?
>Thanks a bunch!

Neither this narrative, nor any of the queries you posted does a very
good job at explaining what you need to get done. A much better way to
ask help in newsgroups is to post the table structure (as CREATE TABLE
statements), some rows of sample data (as INSERT statements) and the
expected output. More on this can be found at www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) Learn the Standard SQL UPDATE syntax, so you will not get Cartesian
explosions problems that will destroy your data integrity.

2) Avoid temp tables in favor of derived tables that the optimizer can
use.

3) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

4) Here is a wild guess. I would replace GM_names with a derived
table.

UPDATE Absenses
SET customer_contact_id
= (SELECT C1.cnt_id
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id)
WHERE EXISTS
(SELECT *
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id);

If the subquery expression returns no rows, you will get a NULL; If it
retursn more than one row, you will get a cardinality violation.|||On 15 Apr 2005 15:21:03 -0700, --CELKO-- wrote:

>4) Here is a wild guess. I would replace GM_names with a derived
>table.
>UPDATE Absenses
> SET customer_contact_id
> = (SELECT C1.cnt_id
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id)
> WHERE EXISTS
> (SELECT *
> FROM Cicntp AS C1, GM_names AS G1
> WHERE C1.cnt_l_name = G1.name
> AND C1.cnt_id = Absences.customer_contact_id);

Hi Joe,

I was starting to post the exact same solution, but then I saw that this
version makes no sense at all - look at the way the subqueries are
joined to the table to be updated: the effect will be that
customer_contact_id will only be changed if it already has the correct
value. In other words: this UPDATE statement might affect some rows, but
it definitely won't change any data!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes. But this is what he wrote when both of us (who are pretty good
SQL guys) trie to understand it. It would be nice to have some DDL and
clear specs instead of bad code ...

Help with Update Query command Problem

Hi all,

I have this store procedure as follows:

Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)

as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email

where ProblemID = @.ProblemID

when I test the querry

exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'

what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.

Please help. I have set the ProblemID as the Primary key.

When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||

Find out the error. where ProblemID = @.ProblemID --> wrong

where @.ProblemID = ProblemID -- Right

cheers

|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?

HELP WITH UPDATE QUERY AND INNER JOIN AND OPENQUERY

If some could show me the syntax for updating a table in my database
with information in a linked table. the table I want to update is
called WorkList and I want to create an inner join query to a linked
table called public.FISCAL. This is all I have so far to create the
join...
SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Now how can I change it to make and Update query on field B.[I-Plan] =
A.[I_PLAN]?
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join SCH.[public].FISCAL
on Account_ID = B.[ACCOUNT#]
"mike11d11" wrote:

> If some could show me the syntax for updating a table in my database
> with information in a linked table. the table I want to update is
> called WorkList and I want to create an inner join query to a linked
> table called public.FISCAL. This is all I have so far to create the
> join...
> SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
> AS A
> INNER JOIN WorkList AS B
> ON A.Account_ID = B.[ACCOUNT#]
> Now how can I change it to make and Update query on field B.[I-Plan] =
> A.[I_PLAN]?
>
|||So this is what I have...
Select * FROM OPENQUERY(SCH,'SELECT [Account_ID], [I_PLAN] FROM
[public.FISCAL]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join A
on Account_ID = B.[ACCOUNT#]
I'll give it a try!
|||Here is the error I get when I use it.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'SCH.public.FISCAL'.
|||anybody have an idea?

HELP WITH UPDATE QUERY AND INNER JOIN AND OPENQUERY

If some could show me the syntax for updating a table in my database
with information in a linked table. the table I want to update is
called WorkList and I want to create an inner join query to a linked
table called public.FISCAL. This is all I have so far to create the
join...
SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Now how can I change it to make and Update query on field B.[I-Plan] =
A.[I_PLAN]?update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join SCH.[public].FISCAL
on Account_ID = B.[ACCOUNT#]
"mike11d11" wrote:

> If some could show me the syntax for updating a table in my database
> with information in a linked table. the table I want to update is
> called WorkList and I want to create an inner join query to a linked
> table called public.FISCAL. This is all I have so far to create the
> join...
> SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]'
)
> AS A
> INNER JOIN WorkList AS B
> ON A.Account_ID = B.[ACCOUNT#]
> Now how can I change it to make and Update query on field B.[I-Plan] =
> A.[I_PLAN]?
>|||So this is what I have...
Select * FROM OPENQUERY(SCH,'SELECT [Account_ID], [I_PLAN] FROM
[public.FISCAL]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join A
on Account_ID = B.[ACCOUNT#]
I'll give it a try!|||Here is the error I get when I use it.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'SCH.public.FISCAL'.|||anybody have an idea?