-
please help me !! SQL
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
-
SQL Query
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
-
tahnk you so much for prompt reply but!!!
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 ???
-
Sample Data
Post your sample data rows and also your update queries, so that we can further look into this..
-
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
-
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.
-
thank you
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
-
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
-
--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--
Last edited by mikr0s; 05-12-2006 at 10:57 AM.
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|