Basic Script Help!

Question

Post reply

Basic Script Help!

JeSuisDicky

SSC Enthusiast

Points: 141
More actions
June 21, 2010 at 2:48 am

#139478

Hello,
Been a member on here for a while but this is my first post :w00t: Hopefully I'm posting this in the correct place.
I've got quite a (or at least I think it is) basic query regarding a script that is going to write the same value to the one column based on the value in another.
The value in the other that is to be referenced always starts the same 'DO NOT USE .......' however then contains a unique string of numbers so I was wanting to use a wildcard lookup, however this does not appear to be getting me anywhere so wondered if anyone could give me any clues????
INSERT INTO docsadm.shellorganisation (disabled) VALUES ('y');
WHERE (((docsadm.shellorganisation.org_name) like 'do not use%'));
If anyone could shed any light on where I'm going wrong it would be much appreciated, I'm a noob so please go easy 😛
Thanks
OMD

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

blue_inelush Ten Centuries Points: 1118 More actions · Answer 1

Hello,

You need to change your script from:

INSERT INTO docsadm.shellorganisation (disabled) VALUES ('y');

WHERE (((docsadm.shellorganisation.org_name) like 'do not use%'));

to something like this:

Insert into docsadm.shellorganisation (disabled)

select 'y' -- what columns do you need

from docsadm.shellorganisation

where docsadm.shellorganisation.org_name like 'do not use%'

Wish you good ideas! 🙂
Andreea

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 2

blue_inelush (6/21/2010)
Hello,
You need to change your script from:
INSERT INTO docsadm.shellorganisation (disabled) VALUES ('y');
WHERE (((docsadm.shellorganisation.org_name) like 'do not use%'));
to something like this:
Insert into docsadm.shellorganisation (disabled)
select 'y' -- what columns do you need
from docsadm.shellorganisation
where docsadm.shellorganisation.org_name like 'do not use%'

Thank you for the super quick reply Andreea 🙂

I'll give that a go in a moment & report back!

Thanks again.

OMD

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What are you trying to do here? Insert new rows into shellorganisation, or update existing rows?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

First, lets build some test data.

DECLARE @shellorganisation AS TABLE(

org_name VARCHAR(50))

INSERT INTO @shellorganisation (org_name)

SELECT 'DO NOT USE ABCDE'

UNION ALL SELECT 'DO NOT USE FGHIJ'

UNION ALL SELECT 'DO NOT USE KLMNO'

UNION ALL SELECT 'DO NOT USE PQRST'

UNION ALL SELECT 'DO NOT USE UVWXY'

UNION ALL SELECT 'DO NOT USE Z1234'

UNION ALL SELECT 'DO NOT USE 56789'

UNION ALL SELECT 'BO NOT USE ABCDE' --fail

DECLARE @othertable AS TABLE(

org_name VARCHAR(50))

Using these two tables, the insert statement would be as follows.

INSERT INTO @othertable (org_name)

SELECT org_name

FROM @shellorganisation

WHERE org_name like 'do not use%'

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Looks like an UPDATE to me.

UPDATE docsadm.shellorganisation

SET [disabled] = 'y'

WHERE docsadm.shellorganisation.org_name LIKE 'do not use%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 6

Chris Morris-439714 (6/21/2010)
What are you trying to do here? Insert new rows into shellorganisation, or update existing rows?

Hi,

I'm trying to add a value (that value being Y) to the same column (Disabled) in each row where 'DO NOT USE....' exists in the same table in another column (ORG_NAME).

I've tried the above script however this isn't working & looking at it select 'y' doesn't appear to be what I'm looking to do....

Hopefully I'm explaining myself fully :blush:

Thanks

OMD

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 7

That looks & appears to make more sense Chris 🙂

I'll give it a whirl.

Thanks

OMD

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

omd (6/21/2010)
Chris Morris-439714 (6/21/2010)
What are you trying to do here? Insert new rows into shellorganisation, or update existing rows?
Hi,
I'm trying to add a value (that value being Y) to the same column (Disabled) in each row where 'DO NOT USE....' exists in the same table in another column (ORG_NAME).
I've tried the above script however this isn't working & looking at it select 'y' doesn't appear to be what I'm looking to do....
Hopefully I'm explaining myself fully :blush:
Thanks
OMD

In that case, as Chris said, the query is: -

--Test Data

DECLARE @shellorganisation AS TABLE(

[org_name] VARCHAR(50),

[disabled] VARCHAR(1))

INSERT INTO @shellorganisation ([org_name],[disabled])

SELECT 'DO NOT USE ABCDE',NULL

UNION ALL SELECT 'DO NOT USE FGHIJ',NULL

UNION ALL SELECT 'DO NOT USE KLMNO',NULL

UNION ALL SELECT 'DO NOT USE PQRST',NULL

UNION ALL SELECT 'DO NOT USE UVWXY',NULL

UNION ALL SELECT 'DO NOT USE Z1234',NULL

UNION ALL SELECT 'DO NOT USE 56789',NULL

UNION ALL SELECT 'BO NOT USE ABCDE',NULL --fail

--Actual Query

UPDATE @shellorganisation

SET [disabled] = 'y'

WHERE [org_name] LIKE 'DO NOT USE%'

--View Results

SELECT * FROM @shellorganisation

/* Results

org_name disabled

-------------------------------------------------- --------

DO NOT USE ABCDE y

DO NOT USE FGHIJ y

DO NOT USE KLMNO y

DO NOT USE PQRST y

DO NOT USE UVWXY y

DO NOT USE Z1234 y

DO NOT USE 56789 y

BO NOT USE ABCDE NULL

*/

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 9

That worked a treat! Thanks Chris & everyone else for there input 🙂

Cheers

OMD

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 10

Chris Morris-439714 (6/21/2010)
Looks like an UPDATE to me.
UPDATE docsadm.shellorganisation
SET [disabled] = 'y'
WHERE docsadm.shellorganisation.org_name LIKE 'do not use%'

Sorry to drag an old thread back up to the top, however I didn't want to create a new one as I have another query based around the same area.....

I'm now wanting to update the same column (disabled) but only where the 'date created' column is June 2005 & before, I haven't a clue how I would set this out? Could anyone shed any light on it?

Thanks

OMD

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

omd (7/7/2010)
Chris Morris-439714 (6/21/2010)
Looks like an UPDATE to me.
UPDATE docsadm.shellorganisation
SET [disabled] = 'y'
WHERE docsadm.shellorganisation.org_name LIKE 'do not use%'
Sorry to drag an old thread back up to the top, however I didn't want to create a new one as I have another query based around the same area.....
I'm now wanting to update the same column (disabled) but only where the 'date created' column is June 2005 & before, I haven't a clue how I would set this out? Could anyone shed any light on it?
Thanks
OMD

UPDATE MyTable SET [disabled] = MyNewValue

WHERE [date created] < 'June 2005'

Edit: This is a basic UPDATE statement, it doesn't get any easier - except you will have to check the data type of the [date created] column and adjust the date constant to match.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 12

Chris Morris-439714 (7/7/2010)
UPDATE MyTable SET [disabled] = MyNewValue
WHERE [date created] < 'June 2005'
Edit: This is a basic UPDATE statement, it doesn't get any easier - except you will have to check the data type of the [date created] column and adjust the date constant to match.

Thanks for the quick reply 🙂

We don't have an in house DBA nor do we have anyone external we can use currently so I'm 'attempting' to cover the few minor bit's that require doing.

So the above would update all dates before 'June 2005'?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

omd (7/7/2010)
Chris Morris-439714 (7/7/2010)
UPDATE MyTable SET [disabled] = MyNewValue
WHERE [date created] < 'June 2005'
Edit: This is a basic UPDATE statement, it doesn't get any easier - except you will have to check the data type of the [date created] column and adjust the date constant to match.
Thanks for the quick reply 🙂
We don't have an in house DBA nor do we have anyone external we can use currently so I'm 'attempting' to cover the few minor bit's that require doing.
So the above would update all dates before 'June 2005'?

No it won't, not without modification.

Have you got backups of your data?

How important to you / your company is your data?

If you don't mind losing it forever then continue. We can play and you will learn loads.

Otherwise, take the time to find someone external who knows what they are doing. And in the meantime, stop. Step away from the keyboard.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 14

Chris Morris-439714 (7/7/2010)
No it won't, not without modification.
Have you got backups of your data?
How important to you / your company is your data?
If you don't mind losing it forever then continue. We can play and you will learn loads.
Otherwise, take the time to find someone external who knows what they are doing. And in the meantime, stop. Step away from the keyboard.

Yes I have & I'm also using a test database so it doesn't matter if it gets trashed.