--#results
SELECT 1 is
projeid	building     bin
1       NOT CHANGE-1 NOT CHANGE-1
2       NOT CHANGE-2 NOT CHANGE-2

SELECT 2 is
projeid	building     bin
1       CHANGED-1    CHANGED-1
2       CHANGED-2    CHANGED-2

CREATE TABLE tab_ccsnetrm(
	projeid int NOT NULL,
	building varchar(100) NOT NULL,
	bin varchar(100) NOT NULL
)

CREATE TABLE table_rm(
	projid int NOT NULL,
	rmtitle varchar(100) NOT NULL
)

CREATE TABLE tab_projects(
	projid int NOT NULL,
	projno int NOT NULL
)

CREATE TABLE Bodega_small(
	clientid int NOT NULL,
	bldg_Title varchar(100) NOT NULL,
	Edificio varchar(100) NOT NULL,
	caja varchar(100) NOT NULL
)

INSERT INTO tab_ccsnetrm VALUES
	(1, 'NOT CHANGE-1', 'NOT CHANGE-1'),
	(2, 'NOT CHANGE-2', 'NOT CHANGE-2')

INSERT INTO table_rm VALUES
	(1, 'OPERACIONES Y PAGOS 08/02/2010 30/12/2010'),
	(2, 'NO SLASHES CONTAINS')

INSERT INTO tab_projects VALUES
	(1, 1),
	(2, 2)

INSERT INTO Bodega_small VALUES
	(1 ,'OPERACIONES Y PAGOS 08/02/2010 30/12/2010', 'CHANGED-1', 'CHANGED-1'),
	(2, 'NO SLASHES CONTAINS', 'CHANGED-2', 'CHANGED-2')

--SELECT 1
SELECT * FROM tab_ccsnetrm

update tab_ccsnetrm set
	[building] = t2.Edificio,
	[bin] = t2.caja
from table_rm t1
inner join tab_projects proj
	on(t1.projid = proj.projid)
inner join Bodega_small t2
	on ltrim(rtrim(proj.projno)) = ltrim(rtrim(t2.clientid))
	and ltrim(rtrim(t1.rmtitle)) = ltrim(rtrim(t2.bldg_Title))

--SELECT 2
SELECT * FROM tab_ccsnetrm

DROP TABLE tab_ccsnetrm
DROP TABLE table_rm
DROP TABLE tab_projects
DROP TABLE Bodega_small

quote:

---

Originally posted by cplusplus


Based on column rmtitle and bldg_title doing updates.

update is not working. when "rmtitle" and "bldg_Title" columns they both have text data like below with slashes that is being compared with in update query,update query getting skipped. only for those rows which has slashes it seems like
'OPERACIONES Y PAGOS 08/02/2010 30/12/2010'


my update query is below:
update tab_ccsnetrm set [building]=t2.Edificio, [bin] = t2.caja
from table_rm t1 inner join tab_projects proj
on(t1.projid = proj.projid)
inner join Bodega_small t2 on(ltrim(rtrim(proj.projno)) = ltrim(rtrim(t2.clientid))) and ltrim(rtrim(t1.rmtitle))=ltrim(rtrim(t2.bldg_Title))

Thank you very much for the helpful info.

---

quote:

---

Originally posted by cplusplus

Hi Visakh,
Please need your advice, even with this query if not exists is showing 876 rows. Many rm.rmtitle's do exist with the projid.
same thing i am using my doubt is in rmtitle it is not macthing. to be safe i am using ltrim rtrim to remove spaces on both ends.

Also i tried with Like operator '% on both ends. that time it was able to pick few rows.



select *
from table_bodega bldg
inner join tab_ccsnetprojects proj on ltrim(bldg.clientid = proj.projno
where not exists
(
select *
from tab_ccsnetrm rm
where rm.projid = proj.projid
and rm.rmtitle = bldg.titulocarpeta
)

---