alternate table value

I need to read through two tables, first stepping thru the first one,
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:

>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48

> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:

>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

altering table with default value

Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO

Altering SQL Field value to Null (DateField)

Hi,
Can someone please help me in resolving this problem.

I am accessing an SQL server from a Web page, when I update a record I sometimes would like to replace a date with a null value. ie. Delete the date in the grid on the Web page and have it remove the date in the database.

I have looked around the web and on this forum and cannot find any information about doing this type of thing.

Someone help would be greatly appreciated.

Thanks..

Regards..

Peter Annandale.Hi Peter,

It depends on the code you're using, but you should be able to set it to DBNull.Value.

If this doesn't work, post your code and we'll try to help you sort it out.

Don|||Don,
since I posted I actually found an article posted by Moorstream in early July about the exact problem I am having. By applying the recommendations of salman_arshad it has fixed my problem.

Thanks for your quick response and assistance.

BTW I had teh right idea with the DBNULL.Value I just wasn't aplying it correctly.

Regards..

Peter Annandale

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:

>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP

alter table set default value for money type column

I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:

>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>

Alter table new column and update

Hi

for MS SQL 2000/2005

I am having a table (an old database, not mine) with char value for the column [localisation]

Users
[name] [nvarchar] (100) NOT NULL ,
[localisation] [nvarchar] (100)NULL

Now i have created a table [Localisation]

Localisation
[id_Localisation] [int] NOT NULL,
[localisation] [nvarchar] (100) NOT NULL

I am adding a new column to Users

ALTER TABLE [Users] ADD
[id_Localisation] int NULL

and I want to update the Column [Users].[id_Localisation] before to drop the column [Users].[Localisation]

something like

UPDATE [Users] SET id_Localisation = (SELECT Localisation.id_Localisation
FROM Localisation FULL OUTER JOIN
Users ON Localisation.Localisation = Users.Localisation)

Users.Localisation can have a NULL value (then no id_localisation return)

but it doesnt work because it returns > 1 row

thank you

how can I do it ?update [Users]
set id_Localisation = t2.id_Localisation
from [Users] t1
inner
join Localisation t2
on t1.Localisation = t2.Localisation|||it works perfectly

thanks a lot

do you thing i have to add a contrainst to this new column ?|||it would be a good idea to declare Users.id_Localisation as a foreign key|||but 5 tables are using this id_Localisation, can i add a FK to each one ?
FK_FK_Users_Localisation
FK_job_Localisation
FK_groups_Localisation
.....

if so

5 times (for each tables)

ALTER TABLE [Users] ADD
id_Localisation int NULL

ALTER TABLE [Users] WITH NOCHECK ADD
CONSTRAINT [FK_Users_Localisation] FOREIGN KEY
(
[id_Localisation]
) REFERENCES [Localisation] (
[id_Localisation]
)

I dont want to apply ON DELETE CASCADE , but to give a Id_localisation = 0 or NULL if a Localisation is deleted, how can i do it
??

thanks again for helping|||I dont want to apply ON DELETE CASCADE, but to give a Id_localisation = 0 or NULL if a Localisation is deleted
You can use ON DELETE SET NULL for that purpose|||but 5 tables are using this id_Localisation, can i add a FK to each one ?yes . ;)|||You can use ON DELETE SET NULL for that purposeunfortunately, not in SQL Server 2000, only in SQL Server 2005|||unfortunately, not in SQL Server 2000, only in SQL Server 2005Ah, right. I checked the wrong manual ;)|||well, i wouldn't exactly call it wrong -- i'm sure it's the right one for SQL Server 2005!!|||thank you

this application must work on 2000 and 2005

ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please helpYou can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
--
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please help
You can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

ALTER TABLE dateAuto

I must add to an existing TABLE a column DateInsert
with a default value = date auto
if a new row is added the column must add datetime.now automaticly (like in acccess 2000) how can I do it ?
for MS SQL 2000
thank youCREATE TABLE #patp (
id INT IDENTITY
, asof DATETIME NOT NULL
DEFAULT GetDate()
, other VARCHAR(10) NULL
)

INSERT #patp (other) VALUES ('One')
INSERT #patp (other) VALUES ('Two')
INSERT #patp (other) VALUES ('Three')

SELECT * FROM #patp
-PatP|||alter table MyTable Add
DateAuto datetime CONSTRAINT DF_DateAuto DEFAULT (GetDate())|||thank you Pat Phelan and hmscott

great ! and fast !!!

Alter table - add default value

Helo Group,
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:

> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>

ALTER Table

I am trying to use the ALTER table Alter column command to set the default
value of a column and to drop the default value of a column. can anyone pls
give me the syntax.I tried the one given in sql help file but gives me a
syntax error.
thanks!To add a default after the table has been created:
alter table <table name>
add constraint <constraint name>
default (<expression> )
for <column name>
To drop a default:
alter table <table name>
drop constraint <constraint name>
Is that what you need?
ML
http://milambda.blogspot.com/|||No i need to alter a column's default... like set a new default value or dro
p
default.but iam getting syntax errors.
"ML" wrote:

> To add a default after the table has been created:
> alter table <table name>
> add constraint <constraint name>
> default (<expression> )
> for <column name>
> To drop a default:
> alter table <table name>
> drop constraint <constraint name>
> Is that what you need?
>
> ML
> --
> http://milambda.blogspot.com/|||You cannot change the default in a single statement. You have to drop the
old default constraint, and add the new one, like ML illustrated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:50D57999-EC50-4E7C-9E04-D538FFB2EE7A@.microsoft.com...
> No i need to alter a column's default... like set a new default value or
> drop
> default.but iam getting syntax errors.
> "ML" wrote:
>
>|||Are you talking about a default as a database object that you have bound to
a
column? Have you tried sp_unbindefault?
ML
http://milambda.blogspot.com/

alter table

Using the alter table command i create a new column on a existing table.
When I creater this new column I want to set the default value as 0. For
some reason the column name is null.
SQL statement:
ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
[ACTIVEFLAGS] [bit] DEFAULT ((0))
Any ideas or the issue with SQL statement?
Thanks,
Big DBig D,
The default value becomes 0 for new rows, but I'll guess what
you want is for the value of this column in existing rows to be 0. In
order to apply a DEFAULT to existing rows, add WITH VALUES to
the statement (and remove NOCHECK, because it doesn't make any
sense for a DEFAULT constraint).
ALTER TABLE [dbo].[DMINFORMATION]
ADD [ACTIVEFLAGS] [bit] DEFAULT 0 WITH VALUES
Steve Kass
Drew University
Big D wrote:

>Using the alter table command i create a new column on a existing table.
>When I creater this new column I want to set the default value as 0. For
>some reason the column name is null.
>SQL statement:
>ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
>[ACTIVEFLAGS] [bit] DEFAULT ((0))
>Any ideas or the issue with SQL statement?
>Thanks,
>Big D
>
>

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong :) )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
--
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>

Alter Font of Data

Hello!

I have a matrix. Inside "Data", I have the follow code:

Fields!Name.Value & Chr(13) & Chr(10) & Fields!Group.Value

Is it possible place Font Bold only at Fields!Name.Value? How?

Thanks

Reporting Services does not support rich text or multiple formats inside text boxes. It is on our wishlist for a future release.

However, you could easily put another textbox in the cell with the Name.Value in a bold font and the other fields in another textbox. You could also add another column.

ALTER COLUMN?

I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

ALTER COLUMN?

I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

alter column to not null that has null values

I have to change numeric columns in 2005 table to not null and default value 0.

What I usually do is an update on the columns setting value to 0 where is null. I know you can use 'with values' when adding a column with default 0 and not null to an existing table.

Can something like this be done for altering a column or do I need to do the update?

Thanks

You need to use UPDATE first and then ALTER. ALTER TABLE table ALTER COLUMN only supports changing the type definition, collation and nullability.