Problem with query

19 May 2006 2:31 PM

meendar

I have the table persons with fields

lastname - varchar
id - numeric

i want to update last name to null if id and lastname are equal. i
tried the following query, but it not works

update persons set lastname=null where lastname like id;

I really appreciate you if you could help me.

Thanks in Advance

19 May 2006 2:38 PM

SQL

Try this

update persons set lastname=null
where lastname = convert(varchar(100),id)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

19 May 2006 7:40 PM

meendar

No it doesn't work!
Please give someother solution

19 May 2006 7:48 PM

Jim Underwood

please post DDL, sample data, and an explanation of what you mean by "it
doesn't work".

Why doesn't it work? Are you getting an error message? Are any rows
updated?

See this link. It explains what we need.
http://www.aspfaq.com/etiquette.asp?id=5006

Show quote

"meendar" <askjavaprogramm***@gmail.com> wrote in message
news:1148067610.329895.147730@u72g2000cwu.googlegroups.com...
> No it doesn't work!
> Please give someother solution
>

19 May 2006 8:12 PM

meendar

DDL

CREATE TABLE [PERSONS] (
    [HONORIFIC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FIRSTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MIDDLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LASTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NICKNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SPOUSE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [ADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [CITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STAFF_REP] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STATUS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SOURCE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CREATE_DATE] [datetime] NULL ,
    [UPDATE_DATE] [datetime] NULL ,
    [NEXT_DATE] [datetime] NULL ,
    [REQ_SALARY] [float] NOT NULL ,
    [RELOCATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRAVEL] [float] NULL ,
    [PURGEDATE] [datetime] NULL ,
    [SSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GNRL_COMMENTS] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [PROF_SUMMARY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FIRSTAVAILABLE] [datetime] NULL ,
    [LASTAVAILABLE] [datetime] NULL ,
    [CITIZENSHIP] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [LOCPREF] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FEETO] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FEETERMS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TITLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMADDRESS_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [COMADDRESS_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [COMCITY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMSTATE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMZIP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMCOUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [SIC] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ASSISTNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [REPNAME] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [START_SAL] [float] NULL ,
    [END_SAL] [float] NULL ,
    [START_DATE] [datetime] NULL ,
    [END_DATE] [datetime] NULL ,
    [RESP_DESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SEXCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RACECODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MARITALSTATUS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [EEOCODEID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EEOGROUPID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [EEOCODE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EEOGROUP] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EEOCAT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EMAILADDR] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STDBILLRATE] [float] NULL ,
    [STDPAYRATE] [float] NULL ,
    [CURBILLRATE] [float] NULL ,
    [CURPAYRATE] [float] NULL ,
    [TYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CLASS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [STAFF_NEXT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [CATEGORY] [int] NOT NULL ,
    [OVERTIMEAFTER] [decimal](11, 4) NULL ,
    [OVERTIMERATE] [decimal](11, 4) NULL ,
    [CURGROSSMARGIN] [decimal](11, 4) NULL ,
    [STDGROSSMARGIN] [decimal](11, 4) NULL ,
    [OVERTIMEEXEMPT] [int] NULL ,
    [DEDUCTIONS] [int] NULL ,
    [PAYTYPE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SALUTATION] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [RESUMEUPDATED] [datetime] NULL ,
    [SICCODEDESC] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [ID] [decimal](12, 3) NOT NULL ,
    [COMID] [decimal](12, 3) NULL ,
    [REPID] [decimal](12, 3) NULL ,
    [CONTACTOK] [int] NULL ,
    [VERIFIED] [int] NULL ,
    [HOMEPRIMARY] [int] NULL ,
    [XDEDUCTIONS] [float] NULL ,
    [ST_DEDUCTIONS] [int] NULL ,
    [ST_XDEDUCTIONS] [float] NULL ,
    [SUI] [int] NULL ,
    [FEDTAX] [int] NULL ,
    [FUTA] [int] NULL ,
    [MCARE] [int] NULL ,
    [SSEC] [int] NULL ,
    [PAYPERIOD] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CREATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UPDATEDBY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WSROWID] [timestamp] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I don't think sample data will be useful. I am not getting any error
messages but the field is not updated.