Still don't see what is wrong with this, do you? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Still don't see what is wrong with this, do you?

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-15 : 23:54:38

I am trying to use a case command to update a column based on information in another column but it is not working any ideas?
The column names and table names are correct. I am trying to use the "%" as a wild card because there are a log of moves to learn and they all start with "Learning". Any help would be nice thanks.

Update PublicINFO Set
PublicINFO.doing = 'Learning a move'
From PublicINFO, PrivetINFO
Where PublicINFO.F_PlayerID=PrivetINFO.F_playerID
and publicinfo.doing = 'Learning%'

Edited by - Eagle_f90 on 04/16/2002 01:21:32

Nazim
A custom title

1408 Posts

Posted - 2002-04-16 : 00:31:05

yeah , i can see

replace = with like operator . Remember wild card characters works only with like not with = when you use column = 'L%' it searches for a exact match for L% rather then the one's starting with L for that to happen you have use Columnname like 'L%'

Update pu Set
Pu.doing = 'Learning a move'
From PublicINFO pu
inner join PrivetINFO Pr
on Pu.F_PlayerID=Pr.F_playerID
where pu.doing like 'Learning%'

NOTE: i replaced your joins from where to inner , this will be more efficient
HTH

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

Edited by - Nazim on 04/16/2002 08:35:53

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-16 : 01:10:33

I am still not able to get it to work, I changed the = to like and it will still not change the publicinfo table, this is the new code I use:

Update PublicINFO Set
PublicINFO.doing = 'Learning a move'
From PublicINFO, PrivetINFO
Where PublicINFO.F_PlayerID=PrivetINFO.F_playerID
and publicinfo.doing like 'Learning%'

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-16 : 07:13:43

Still does not change the rows at all.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-16 : 07:47:36

I don't know if this will help, but it might:

UPDATE PublicINFO
SET doing = 'Learning a move'
FROM PublicINFO INNER JOIN PrivetINFO ON PublicINFO.F_PlayerID=PrivetINFO.F_playerID
WHERE publicinfo.doing LIKE 'Learning%'

The ANSI JOIN style is the preferred method of joining tables together in a query. The WHERE syntax may not be optimizing properly. Also, when using the SET statement, you cannot include a table alias with the column name.

Nazim
A custom title

1408 Posts

Posted - 2002-04-16 : 08:35:02

Rob, can you explain more on this. it doesnt goes in my head

quote:

Also, when using the SET statement, you cannot include a table alias with the column name.

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-16 : 08:47:48

This is how it's listed in Books Online for the UPDATE statement:

SET

Specifies the list of column or variable names to be updated.

column_name

Is a column that contains the data to be changed. column_name must reside in the table or view specified in the UPDATE clause. Identity columns cannot be updated.

If a qualified column name is specified, the qualifier must match the table or view name in the UPDATE clause. For example, this is valid:

UPDATE authors
    SET authors.au_fname = 'Annie'
    WHERE au_fname = 'Anne'

A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. For example, this is not valid:

UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

To make the example work, remove the t. alias from the column name.

UPDATE titles
    SET ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

Now I have written UPDATE statements using the "illegal" syntax and for some reason they worked fine. It could be some minor semantic issue that makes one UPDATE work and not another, but in any case, that's the way it's supposed to be written.

Nazim
A custom title

1408 Posts

Posted - 2002-04-16 : 09:38:30

i have always followed this syntax without any problem. Any reasons you know of why this isnt encouraged.

UPDATE t SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

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

Edited by - Nazim on 04/16/2002 09:40:45

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources