please help me !! SQL

[rainman]

hi
I am new to SQL server. I am having trouble with how to do this --- example -- Table A and B --

table A fields
ID
Name
PT
Zip

Table B fields
ID
ZIP

now I need to if in Tabe A's Zip code match Table B's zip code, then Table A's PT 2 or else 1, PT will be updated autometically depanding on that table B's zip code.

It would be greatly apreciated if anyone can please urgently reply, and like i said i am new please explain it too, my job is on the line.

thank you
neal

[Kots]

Hi,
Here is one way of doing it. Hope this helps !! --Kots.

--table1
create table a(ID smallint,nam varchar(30),point smallint,zip char(5))
go

--table2
create table b(id smallint, zip char(5))
go

--populate tab1 with test data
insert into a
select 1,'BOB',null,'01234'
insert into a
select 2,'SAM',null,'05678'
insert into a
select 3,'TARA',null,'09876'
insert into a
select 4,'CHRIS',null,'07890'

--populate tab2 with test data
insert into b
select 1,'01234'
insert into b
select 2,'02678'
insert into b
select 3,'03456'
insert into b
select 4,'07890'

--total rows from table1
select * from a
go

--total rows from table2
select * from b
go

--First Update PT (point) if the zip codes are matching
update a
set point = 2
from
a,
b
where
a.id = b.id
and a.zip = b.zip
go

--Then Update PT (point) for non matching rows.
update a
set point = 1
from
a,
b
where
a.id = b.id
and a.zip <> b.zip
go

--results to see the updated data from table1
select * from a

[rainman]

thank you so much for prompt reply , but for some reason its shoing null record on PT (point) field when i am trying query analyzer its working
not sure what i am doing wrong is there any otherway to do this ???

[Kots]

Post your sample data rows and also your update queries, so that we can further look into this..

[rainman]

CREATE TABLE [dbo].[employer_tbl] (
[Employer_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[P_ID][bigint] not null,
[PCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO


--table1
--create table a(ID smallint,nam varchar(30),point smallint,zip char(5))
--go


--table2
--create table b(id smallint, zip char(5))
--go

CREATE TABLE [dbo].[p_tbl] (
[P_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[PCode] [bigint] NOT NULL
) ON [PRIMARY]
GO

---------------------------------------------------------------------------------
--total rows from table1
select * from employer_tbl
go

--total rows from table2
select * from p_tbl
go

--First Update PT (point) if the zip codes are matching
update employer_tbl
set Employer_tbl.P_ID = 1
from
employer_tbl,
p_tbl
where
employer_tbl.P_ID = p_tbl.ID
and employer_tbl.pcode = promocode_tbl.pcode
go

--Then Update PT (point) for non matching rows.
update Employer_tbl
set Employer_tbl.P_ID = 2
from
Employer_tbl,
p_tbl
where
employer_tbl.P_ID = p_tbl.ID
and employer_tbl.pcode <> p_tbl.pcode
go


--results to see the updated data from table1
select * from employer_tbl
--select * from p_tbl

employer_tbl
employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

::: on # 5 P_id should be 1 but its coming as 0

p_tbl

Pr_ID P_ID pcode
1 1 111
2 1 222
3 1 223
4 1 333

[Kots]

We need to have matching ID on both the tables.
If you look at p_table , you have only 4 records instead of 5.

employer_tbl
employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

::: on # 5 P_id should be 1 but its coming as 0
p_tbl

Pr_ID P_ID pcode
1 1 111
2 1 222
3 1 223
4 1 333

--Kots.

[rainman]

is there anyway possible only P_ID to reflect i have maching data on other table or not instead of having to do anything with employer_ID or Pr_ID

thank you for all your valuable suggestion. i apreciate all your help

neal

[rainman]

i have treid with matching IDS with same # of records but still its not showing the right thing --- please help me !!!

1 2 111 NULL
2 2 321 NULL
3 1 222 NULL
4 3 111 NULL
5 2 222 NULL

i had 5 records for both table, above record # 5, 4, 3, and 1 should have 1 P-ID instead of 2

[mikr0s]

--for MSSQL server
UPDATE A
SET PT = CASE WHEN B.zip IS NOT NULL THEN 2 ELSE 1 END
FROM A
LEFT JOIN B ON B.zip = A.zip --AND any other join conditions

--HTH--

[rainman]

thanks for your reply but i am not seeking help with what i have in null and declaring 1 or 2 depanding on that -- my question was
employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

P_ID 1 or 2 if i have P_code in another table suppose in table A --

Pr_ID P_ID pcode
1 1 111
2 1 222
3 1 223
4 1 333

now B table's-- P_ID will be updated or changed, if an user input Pcode in B table, If Pcode exists on A table than B table P_ID 1 or else 2

employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

thank you
neal

[rainman]

thanks for your reply but i am not seeking help with what i have in null and declaring 1 or 2 depanding on that -- my question was
employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

P_ID 1 or 2 if i have P_code in another table suppose in table A --

Pr_ID P_ID pcode
1 1 111
2 1 222
3 1 223
4 1 333

now B table's-- P_ID will be updated or changed, if an user input Pcode in B table, If Pcode exists on A table than B table P_ID 1 or else 2

employer_id P_ID Pcode company_Name
1 1 111 NULL
2 1 222 NULL
3 1 333 NULL
4 0 543 NULL
5 0 111 NULL

please help me !!!!!

thank you
neal