i have a problem in my query in database using LIKE

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Pls help me. what i want is, I want to Insert all the prefixNo in Table1 to prefixNo of table2 base on the ContactNo. So all the NULL in Table2 in PrefixNo will be change into 3 character in prefixNo in table1.

SQL

UPDATE  table2 SET  prefixNo = (SELECT prefixNo WHERE Table1 LIKE prefixNo)  WHERE contactno LIKE (SELECT prefixNo WHERE Table1 LIKE prefixNo) ORDER BY prefixNo

It's not working. I try different type of coding but it doesn't work. :(. what is problem? pls help me.

table1

SQL

id  |    prefixNo
1   |   910
2   |   922
3   |   920
4   |   929
5   |   930
6   |   932
7   |   933

table2

VB

id  |   prefixNo|   ContactNo
1   |   NULL    |   9309556384
2   |   NULL    |   9307815328
3   |   NULL    |   9334489301
4   |   NULL    |   9330000024
5   |   NULL    |   9108456218
6   |   NULL    |   9305789621
7   |   NULL    |   9335554868
8   |   NULL    |   9224813354
9   |   NULL    |   9222224568
10  |   NULL    |   9105789545

ok. so this is the output i want to display

OUTPUT:

VB

Table 2
id  |   prefixNo|   ContactNo
1   |   930 |   9309556384
2   |   930 |   9307815328
3   |   933 |   9334489301
4   |   933 |   9330000024
5   |   910 |   9108456218
6   |   930 |   9305789621
7   |   933 |   9335554868
8   |   922 |   9224813354
9   |   922 |   9222224568
10  |   910 |   9105789545

Posted 5-Aug-13 18:05pm

Gishisoft

Updated 6-Aug-13 17:49pm

v6

Add a Solution

Comments

[no name] 6-Aug-13 0:11am

Is there any error you are getting..??

Gishisoft 6-Aug-13 0:33am

yes

Thanks7872 6-Aug-13 1:06am

What you want as the final value 310200 or 310200%? I think 310200% is lil bit confusing.

Gishisoft 6-Aug-13 20:42pm

i will show you more info to make it easier for you to understand.

Gishisoft 6-Aug-13 21:06pm

I improve my quest. pls take a look at my problem.

ArunRajendra 6-Aug-13 23:29pm

So you want to updat the prefix no if its there in the contact No? When you are using like the value can be present any where so there are chances a record will get updated multiple times and last one will the final result is this ok?

Gishisoft 7-Aug-13 2:58am

yes!

4 solutions

Solution 1

update-row-with-sql-statement-with-like-fails.html[^]

Try this way..hope you will get it...

Posted 5-Aug-13 18:12pm

Code-Hunt

Solution 2

SQL

UPDATE  TABLE2  SET
prefixNo=B.prefixNo  FROM TABLE1 B WHERE contactno LIKE B.prefixNo+'%'

THIS CAN GET WHAT YOU WANT

Posted 6-Aug-13 17:22pm

qqlww1987

Comments

Gishisoft 7-Aug-13 1:00am

this is the error. when i try this. the prefixNo nand contact number are numeric. but the error is looks like this. I didn't declare any varchar here.

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

Solution 4

If you want,you can Cast numeric as varchar.this can use SQl ‘like’.If you only aim is to achieve，

SQL

UPDATE  TABLE2
SET prefixNo= left(ContactNo, 3)

Is a good choice.But to ensure that your data format

like this

SQL

UPDATE  TABLE2  SET
prefixNo=B.prefixNo  FROM TABLE1 B WHERE contactno LIKE   CAST( B.prefixNo AS VARCHAR)+'%'

Posted 6-Aug-13 22:29pm

qqlww1987

Updated 6-Aug-13 22:30pm

v2

Comments

Gishisoft 7-Aug-13 4:41am

thank you!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2013-08-06T19:00:00

Solution 3

Try this,

SQL

UPDATE  TABLE2  
SET prefixNo= left(ContactNo, 3)

Posted 6-Aug-13 19:00pm

_Asif_

Comments

Gishisoft 7-Aug-13 1:18am

Thank you so much! it works. after to do this. how to apply it pivot? in order i can group all the same number having same PrefixNo